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

Yashwant Singh

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"...

Paul Newcome

Try this one...

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

Yashwant Singh

Hi Paul,

formula is not working... please suggest..

Genevieve P.

Hi @Yashwant Singh

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

Yashwant Singh

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.

Paul Newcome

@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.

Yashwant Singh

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

Paul Newcome

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

@cell <> "N/A"

Yashwant Singh

please check formula :

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

result is : 0 which is incorrect.

Paul Newcome

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

Paul Newcome

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

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

Yashwant Singh

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

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

Genevieve P.

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?

Paul Newcome

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.

Genevieve P.

Oh good catch, @Paul Newcome!

Yashwant Singh

@Paul Newcome Checked my range and also small black arrows....but still not resolved issue..

@Genevieve P criteria applied for 2 columns..

1st check if columns "EFT" is blank and 2nd check if "Date EFT Payment Made" is not equal to "NA"..