Why Does this Sumproduct Formula Give a Different Result to Countifs?

[rob51852]

Hi,

Can anyone tell me why the Sumproduct formula returns a different result to the Countifs formula? The Countifs gives the correct result.

Thanks

=SUMPRODUCT((DataTable[Lowest Delta]>=$A8)*(DataTable[Result]="LOSER"))
=COUNTIFS(DataTable[Lowest Delta],">=" &$A8,DataTable[Result],"LOSER")

 

[arthurbr]

And what does SUMPRODUCT return?

 

[FormR]

Hi, it would help if you posted some sample data that demonstrated the problem, but it could be that whatever is in A8 is not the same data type as whatever is in the "Lowest Delta" column. i.e. A8 might be text and "Lowest Delta" numeric. COUNTIFS() can be a little more forgiving in situations like this. For example:

Book2
	A	B	C	D	E
1
2
3
4
5
6
7
8	1	< Text
9
10	Lowest Delta	Is Number	Result		0
11	2	TRUE	LOSER		3
12	2	TRUE	LOSER
13	2	TRUE	LOSER
Sheet1

Cell Formulas
Range	Formula
A8	A8	="1"
E10	E10	=SUMPRODUCT((DataTable[Lowest Delta]>=$A8)*(DataTable[Result]="LOSER"))
E11	E11	=COUNTIFS(DataTable[Lowest Delta],">=" &$A8,DataTable[Result],"LOSER")
B11:B13	B11	=ISNUMBER([@[Lowest Delta]])

 

[Alex Blakenburg]

The above is one scenario, another is that the expression used by SumProduct being (DataTable[Lowest Delta]>=$A8) will evaluate anything in "Lowest Delta" that is text as being greater than whatever number you have in A8.
This includes any formulas in that column that return "".
Countifs appears to just ignore any text values in the Criteria_Range ie Lowest Delta.

If the only text values in the column are "" then you could try this:

=SUMPRODUCT((DataTable[Lowest Delta]>=$A8)*(DataTable[Result]="LOSER")*(DataTable[Lowest Delta]<>""))

If you have other text values as well then this should work:

=SUMPRODUCT((DataTable[Lowest Delta]>=$A8)*(DataTable[Result]="LOSER")*ISNUMBER(DataTable[Lowest Delta]))