# how to use "<>N/A" formula in smartsheet Date column

edited 07/09/20

hi,

using countif() formula for count total count of "<>N/A" cells. when use formula

=COUNTIF({Master Tax Calendar Range 3}, "<>N/A") and my count is zero. my column format is Date.

same formula count correctly in excel but not working in smartsheet.

please suggest formula for how to count total, if my date column contain "N/A"...

«1

• ✭✭✭✭✭✭

Try this one...

=COUNTIF({Master Tax Calendar Range 3}, @cell <> "N/A")

• Hi Paul,

formula is not working... please suggest..

• Employee

These <> indicate that you're looking for everything that's not "N/A", is that correct?

If you're looking to count everything that IS "N/A", you can just add it as the criteria:

=COUNTIF({Master Tax Calendar Range 3}, "N/A")

If this doesn't work, it would be helpful to see a screen capture of the column you're referencing in {Master Tax Calendar Range 3}, but please block out any sensitive data.

Thanks!

Genevieve

October 8 - 10, Seattle, WA | Register now

• edited 07/09/20

Hi Genevieve,

Yes, <> indicate that not looking for everything that's not "N/A".

Please check below in yellow color.. count for : If "EFT" column is blank and "Date EFT Payment Made" column is not N/A.

• ✭✭✭✭✭✭

@Genevieve P Good catch! Thanks!

@Yashwant Singh Please see Genevieve's suggestion above. I had missed the part that you were tying to count the cells that were "N/A" and simply corrected the syntax. My apologies.

• @Paul Newcome , "N/A" count is correct but i need count of "<>N/A".

• ✭✭✭✭✭✭

If you need a count of cells that are NOT "N/A" then your criteria would be

@cell <> "N/A"

=COUNTIF({Master Tax Calendar Range 6}, {Master Tax Calendar Range 6} <> "N/A")

result is : 0 which is incorrect.

• ✭✭✭✭✭✭

You are missing a criteria for your first range and need a comma between your second range and it's criteria.

• ✭✭✭✭✭✭

I just noticed both of your ranges are the same. Try this exactly as is...

=COUNTIF({Master Tax Calendar Range 6}, @cell <> "N/A")

• formula is incorrect and result is 0 again.....

I have shared my columns details in above discussion, could you please check and share formula...

• Employee

Hmm, why don't we try searching for something, instead of using "not" <>.

Since it's a date column, let's try the ISDATE function:

=COUNTIF({Master Tax Calendar Range 6}, ISDATE(@cell))

@Yashwant Singh , do you also need to add in the other column & criteria, of EFT being not blank?

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭

Are you entering the formula exactly as is including "@cell" (without the quotes)?

Have you double checked your range in your cross sheet reference? When viewing a sheet that is being used as a reference for a formula in another sheet, there should be small black arrows in the bottom of each cell within the range. I do not see that in your screenshots. You may want to check to ensure the proper range is selected.

• Employee

Oh good catch, @Paul Newcome!