"Sumif" leads to wrong results

[HongRu]

Hi guys!

"Sumif" leads to wrong result at the range("E2").

Why does it lead to the wrong result？

How to fix or avoid the wrong result？

I would appreciate this.
HongRu

sumif.xlsx
	A	B	C	D	E
1	Item	Price		Item	Price
2	1-13	1		1-13	321
3	13-1	20
4	13-1	300
sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMIF(A1:A4,D2,B1:B4)

 

[Alex Blakenburg]

I used to think of SumIf / CountIf etc as being more being more forgiving in terms of mixed data types but in your case it's working against you.
I can replicate your issue by changing my Windows Short Date to yyyy-mm-dd and I can't find a way around it using SumIf or SumIfs.
It looks like Sum/SumProduct (or Sum + Filter) as you have already identified are your best options.

 

[HongRu]

I used to think of SumIf / CountIf etc as being more being more forgiving in terms of mixed data types but in your case it's working against you.
I can replicate your issue by changing my Windows Short Date to yyyy-mm-dd and I can't find a way around it using SumIf or SumIfs.
It looks like Sum/SumProduct (or Sum + Filter) as you have already identified are your best options.

View attachment 114620

Thanks for your reply.

 

[Alex Blakenburg]

Thanks for raising the issue. It has provided a bit more insight into how the SumIf group of functions convert both the range and criteria to numbers where they can under the hood.