Count the number of cells in a given year with data

[remobec]

Hello!

I asked a similar question here: https://www.mrexcel.com/forum/excel...ber-cells-number-corresponding-date-year.html

There, I was trying to count the days in a month that had data. Now I'm trying to count the months in a year that have data.

So, right now my data looks like this:

A B

<colgroup><col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl65, width: 68, align: right"]1/1/1963
[/TD]
[TD="width: 64, align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]2/1/1963[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]3/1/1963[/TD]
[TD="align: right"]0[/TD]

[TD="class: xl65, align: right"]4/1/1963[/TD]
[TD="align: right"]1.8
[/TD]

[TD="class: xl65, align: right"]5/1/1963[/TD]
[TD="align: right"]5.07
[/TD]

[TD="class: xl65, align: right"]6/1/1963[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]7/1/1963[/TD]
[TD="align: right"]0
[/TD]

[TD="class: xl65, align: right"]8/1/1963[/TD]
[TD="align: right"]1.33
[/TD]

[TD="class: xl65, align: right"]9/1/1963[/TD]
[TD="align: right"]0.36
[/TD]

[TD="class: xl65, align: right"]10/1/1963[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]11/1/1963[/TD]
[TD="align: right"]4.83
[/TD]

[TD="class: xl65, align: right"]12/1/1963[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]1/1/1964[/TD]
[TD="align: right"]2.5
[/TD]

[TD="class: xl65, align: right"]2/1/1964[/TD]
[TD="align: right"]1.29
[/TD]

[TD="class: xl65, align: right"]3/1/1964[/TD]
[TD="align: right"]2.05
[/TD]

[TD="class: xl65, align: right"]4/1/1964[/TD]
[TD="align: right"]6.36
[/TD]

[TD="class: xl65, align: right"]5/1/1964[/TD]
[TD="align: right"]0.74
[/TD]

[TD="class: xl65, align: right"]6/1/1964[/TD]
[TD="align: right"]1.37
[/TD]

[TD="class: xl65, align: right"]7/1/1964[/TD]
[TD="align: center"]#N/A[/TD]

[TD="class: xl65, align: right"]8/1/1964[/TD]
[TD="align: right"]3.05
[/TD]

[TD="class: xl65, align: right"]9/1/1964[/TD]
[TD="align: right"]7.81
[/TD]

[TD="class: xl65, align: right"]10/1/1964[/TD]
[TD="align: right"]2
[/TD]

[TD="class: xl65, align: right"]11/1/1964[/TD]
[TD="align: right"]2.84[/TD]

[TD="class: xl65, align: right"]12/1/1964[/TD]
[TD="align: right"]0.26


[/TD]

</tbody>

I would like to count the months that have data (whether 0 o 2 or 3 or whatever) and exclude the ones that have #n/a.

So, for 1963, it should come up with 7 and for 1964 it should come up with 11.

I tried tweaking the formula that worked for counting the days in the month, but this is really beyond me. This is the similar formula that work for that (Thanks to Aladin Akyurek)

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)))

Thank you!

 

[Marcelo Branco]

Assuming your data in A2:B25 try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Year​

[/TD]
[TD]

Formula​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1963​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1964​

[/TD]
[TD]

11​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in E2 copied down
=SUMPRODUCT(--(YEAR(A$2:A$25)=D2),--ISNUMBER(B$2:B$25))

M.

 

[Tetra201]

Or this:

=COUNTIFS(A2:A25,">=1/1/"&D2,A2:A25,"<1/1/"&D2+1,B2:B25,"<>#N/A")