Count the Number of Cells with a number in a Corresponding Date & Year

[remobec]

I have 100 years of rainfall data. Each line represents one day. On some days, I have no data. This is represented by #n/a. On other days, I have the amount of rain that fell, represented by a number.


What I want to do is count how many days in a given month numerical values are recorded. So it would look something like this:


A B C D
January 1 1900 0
January 2 1900 .5
January 3 1900 #n/a
January 4 1900 0
January 5 1900 2.3
January 6 1900 0
January 7 1900 .25
January 8 1900 0


So... Count Column D if it's a numeric value and A is January and C is 1900. The value here should be "7". (If it's easier, I can also do the reverse and count the number of times #n/a shows up, which would be "1.")

I keep googling the Count or Countif functions, but I can't figure out how to get it to do what I want.

Thank you!

 

[Aladin Akyurek]

What do you have in B?

And does A contain literally items like January 1 which appears to be text?

 

[remobec]

I have January in A, 1 in B and 1900 in C. I simplified my spreadsheet to post here, but I also have a column with the entire date that I can work from. With my figure-it-out-as-I-go approach to Excel, at one point I found separating the month and year made it easier for me.

But either way will work--pull the month and year from "January 1, 1900" or align the month column and the year column.

 

[Aladin Akyurek]

Assuming that the full date is in E (adjust to suit)...

Control+shift+enter, not just enter:

=SUM(IF($E$2:$E$400-DAY($E$2:$E$400)+1=DATE(1900,1,1),IF(ISNUMBER($D$2:$D$400),1)))

You can replace DATE(1900,1,1) with say F2 which houses a first day date like 1/1/1900, indicative of January 1900.

 

[remobec]

Aladin, thank you. That formula works great!

 

[Aladin Akyurek]

Aladin, thank you. That formula works great!

You are welcome.