Excel countifs help

[bazzabear]

Hi everyone

I have a spreadsheet in which column A has a country name, and then other columns have dates in them (or have #N/A).

is there a way to simply count how many dates there are against a country in a particular column?

I get as far as:

=COUNTIFS('tab name!A:A',"China",'tab name!B:B,????)

But what do I enter for the ???? to say "is a date", or possibly "Is not #N/A"?

Thanks in advance for any help you can offer!

(If that's too easy for you - is it possible to get it to count the dates against a particular country across multiple columns?)

 

[dazzerd]

There is no inbuilt function to check if a cell value is a date.

Easiest thing to do without writing a UDF is to check if it is not n/a:

=COUNTIFS('tab name!A:A',"China",'tab name!B:B,"<>#N/A")

 

[bazzabear]

Cheers. I suspect that while the #N/A entries originate from a formula, they have been copy-pasted as text entries. Will that still work do you know?

 

[Antar Bolaeisk]

You could alternatively use ">0" as the criteria as all dates should be stored as the number of days since 01/01/1900 and therefore must be a number greater than 0. The #N/A won't be counted.

 

[dazzerd]

Cheers. I suspect that while the #N/A entries originate from a formula, they have been copy-pasted as text entries. Will that still work do you know?

It should work with both

 

[dazzerd]

You could alternatively use ">0" as the criteria as all dates should be stored as the number of days since 01/01/1900 and therefore must be a number greater than 0. The #N/A won't be counted.

That would work fine so long as you don't actually have numbers in the column.

 

[bazzabear]

Thanks Chaps!

Am I right that doing this count across multiple columns won't work simply (or at all)?

 

[Mirmo]

Form ultiple columns just repeat your formula with the other cell references

=countif(...)+countif(...)