Share via

Error due to blank cell in countif by month using sumproduct

Anonymous
2015-04-19T08:14:51+00:00

I have a data like this. Wanted to count number of entries in month

12-Apr-15 Month Output
30-Sep-15 4 1 =SUMPRODUCT(1*(MONTH($A$1:$A$17)=C2))
5 0 =SUMPRODUCT(1*(MONTH($A$1:$A$17)=C3))
28-Oct-15 6 0
01-Nov-15 7 0
8 0
17-Nov-15 9 1
10 1
11 2
04-Jan-16 12 0
26-Jan-16 1 7 =SUMPRODUCT(1*(MONTH($A$1:$A$13)=C11))
2 1
03-Feb-16 3 0

C2,C3,C4 refer to month column ie C2 refers to 4 (April). Every answer is coming correct in output column. However, in the month of January actual entries are only two, however, answer is coming as 7. All the 5 number of Blanks also added in January ie blanks being treated as 1 for January.

Can anybody suggest how to resolve this error

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2015-04-19T08:21:23+00:00

Hi,

Try it this way.

=SUMPRODUCT((MONTH($A$1:$A$17)=C2)*($A$1:$A$17<>""))

Was this answer helpful?

2 people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-04-19T10:06:39+00:00

    Working fine

    Thanks

    Glad that worked, you might now like to mark my response as answer.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-04-19T09:52:25+00:00

    Working fine

    Thanks

    Was this answer helpful?

    0 comments