Importing equation from Excel

Hani Al Ashkar

I imported a sheet with the below equation from Excel. However,it comes back with #UNPARSEABLE and i'm not sure where it's going wrong on Smartsheet, any help or advice would be highly appreciated.

=COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,">="&TODAY()-30)+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Weekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Daily")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Biweekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Monthly")

Thanks and Rgds

Hani

Genevieve P.

Hi Hani,

It looks like you just need to adjust the TODAY Function... in Smartsheet you put the number of days within the (parentheses), and you don't have quotes around the equal sign.

Try this:

=COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387, >=TODAY(-30))+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Weekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Daily")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Biweekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Monthly")

Are you meaning to reference only until row 387, or do you want the entire column referenced? If you want to make sure this formula auto-updates as new rows are added, you can take out the row numbers to reference the entire column, continuously... ex: [Visit Interval]:[Visit Interval]

(See here for more information on referencing columns.)

Try this:

=COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date], >=TODAY(-30))+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Weekly")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Daily")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Biweekly")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Monthly")

The other thing to note is that I see you're using the column [Last FSR Airplane Visit Date] as a date type of column that also contains text... is that correct? This formula is looking for a date (with the TODAY function, saying within the last 30 days) and also for text, "Weekly", or "Daily", etc. I just want to make sure that was intended.

If this formula doesn't work, it would be useful to see a screen capture of your sheet showing these columns, but please block out any sensitive data!

Cheers,

Genevieve

Genevieve P.

Hi Hani,

It looks like you just need to adjust the TODAY Function... in Smartsheet you put the number of days within the (parentheses), and you don't have quotes around the equal sign.

Try this:

=COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387, >=TODAY(-30))+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Weekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Daily")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Biweekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Monthly")

Are you meaning to reference only until row 387, or do you want the entire column referenced? If you want to make sure this formula auto-updates as new rows are added, you can take out the row numbers to reference the entire column, continuously... ex: [Visit Interval]:[Visit Interval]

(See here for more information on referencing columns.)

Try this:

=COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date], >=TODAY(-30))+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Weekly")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Daily")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Biweekly")+COUNTIFS([Visit Interval]:[Visit Interval],"",[Last FSR Airplane Visit Date]:[Last FSR Airplane Visit Date],"Monthly")

The other thing to note is that I see you're using the column [Last FSR Airplane Visit Date] as a date type of column that also contains text... is that correct? This formula is looking for a date (with the TODAY function, saying within the last 30 days) and also for text, "Weekly", or "Daily", etc. I just want to make sure that was intended.

If this formula doesn't work, it would be useful to see a screen capture of your sheet showing these columns, but please block out any sensitive data!

Cheers,

Genevieve

Hani Al Ashkar

Genavieve, thank you very much for your support! it worked!

You are correct, the column [Last FSR Airplane Visit Date] is a date/text column. That is intended.

I have applied the Today format you recommended on other equations and its worked apart from the below. Can you please take a look at the below and let me know why it is not working? Is it because of the combined text/date column that the function is looking for?

Thanks for your help!

=COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,=>TODAY(-30))+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Weekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Daily")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Biweekly")+COUNTIFS([Visit Interval]1:[Visit Interval]387,"",[Last FSR Airplane Visit Date]1:[Last FSR Airplane Visit Date]387,"Monthly")

Genevieve P.

Hi @Hani Al Ashkar

I'm very glad to hear that the TODAY function is working as you would like it to!

In regards to this formula, could you be a bit more specific? Is it giving you an error or an incorrect outcome? It would be very helpful to see how this actually looks in your sheet, with the formula in the cell opened up. Are you able to post a screen capture?