Find month and count second column

bart_s · July 4, 2021, 9:07am

I have a sheet where A4:A368 contain all dates for the current year. In column B (B4:B368) I enter data for a day if some event has happened. So it may be that column B contains data (number or text) or remains blank.

Now I want to count the number of events that happened in 1 month

With =SUMPRODUCT(MONTH(A4:A368)=1,B4:B368) I can get the summation of the events, but I would like to have the count (aka number of events).

I tried with =COUNTIF(MONTH(A4:A368), "=1") which gives an error (504) and =COUNTIF(B4:B368,MONTH(A4:A368)=1) which always returns 0

Is there anyway I can find all days of a month in column A and count the number of cells that contain data for that month ?

anon73440385 · July 4, 2021, 9:15am

I have a sheet where A4:A386 contain all dates for the current year

this are more than 365 rows so the question is: Are some dates existing more than once (may be important for the solution) or is it a typo an should read A4:A368 (which are 365 rows). From your formulas I tend to assume a typo.

bart_s · July 4, 2021, 9:44am

typo → sorry, thanks for bringing that to my attention.

PKG · July 4, 2021, 9:19am

e.g.: =SUMPRODUCT(MONTH(A4:A368)=1,B4:B368<>"")

bart_s · July 4, 2021, 9:42am

I reacted too soon. Sorry. its because the name of the function confused me. The formula is working. Thanks

anon73440385 · July 4, 2021, 9:25am

Hello

may be =SUMPRODUCT(MONTH(A4:A368)=1;(B4:B368)<>"") is what you are looking for to count the number of event entries in column B for month 1 (January).

bart_s · July 4, 2021, 9:46am

I reacted too soon. Sorry. its because the name of the function confused me. The formula is working. Thanks