Sumproduct returning wrong result

[Deutz]

Hi and thanks in advance,

I am trying to return a count of rows containing dates by month.

As per the image below, I am using the following formula to get the counts in column C ...

=SUMPRODUCT(1*(MONTH('QA Dashboard'!$I$4:$I$120)=A19))

(where column A has the appropriate month number)

All months except January are returning the correct count. The date range on another sheet contains 117 cells and January is picking up all the cells that contain blanks for some reason. I have deleted all values from the date columm, reinput the dates but keep getting the same result.

Kind regards
Deutz

 

[Cubist]

Blank cells are stored as 1/0/1900 so MONTH will return 1. You'd need another condition to filter out the blanks. Try:

=SUMPRODUCT(('QA Dashboard'!$I$4:$I$120<>"")*(MONTH('QA Dashboard'!$I$4:$I$120)=A19))

 

[Deutz]

Blank cells are stored as 1/0/1900 so MONTH will return 1. You'd need another condition to filter out the blanks. Try:

=SUMPRODUCT(('QA Dashboard'!$I$4:$I$120<>"")*(MONTH('QA Dashboard'!$I$4:$I$120)=A19))

Thanks Cubist, that makes sense.

I have tested your solution and it works. Thanks ... much appreciated!

 

[Cubist]

You're welcome.
Side note: with newer version, you can simply use SUM instead of SUMPRODUCT.

 

[Deutz]

You're welcome.
Side note: with newer version, you can simply use SUM instead of SUMPRODUCT.

Thanks, that is good to know ... Cheers

 

[Peter_SSs]

Your range in QA Dashboard is not very big so this will likely not be an issue for you but SUMPRODUCT is not a very efficient function. You may not have it yet but if you do (or when you do get it) then you can use the GROUPBY function instead. As well as eliminating the SUMPRODUCT function, this also only requires this formula in the top cell and does not need to be copied down.
I have included the previous formula in column C so that you can see that the results are the same.

At least this function might be something to keep in mind for the future even if you choose not to use it here.

Deutz.xlsm
	A	B	C	D
13	7		4	4
14	8		4	4
15	9		2	2
16	10		5	5
17	11		1	1
18	12		1	1
19	1		7	7
20	2		1	1
21	3		3	3
22	4		0	0
23	5		2	2
24	6		2	2
Sheet1

Cell Formulas
Range	Formula
C13:C24	C13	=LET(d,'QA Dashboard'!I4:I120,IFNA(VLOOKUP(A13:A24,GROUPBY(MONTH(d),d,COUNT),2,0),0))
D13:D24	D13	=SUMPRODUCT(('QA Dashboard'!$I$4:$I$120<>"")*(MONTH('QA Dashboard'!$I$4:$I$120)=A13))
Dynamic array formulas.