"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)

 

[Phuoc]

It works for me.

Book1
	A	B	C	D	E
1	Item	Price		Item	Price
2	1-13	1		1-13	1
3	13-1	20
4	13-1	300
Sheet1

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

 

[StephenCrump]

I suggest you have a close look at what's actually in cells A2:A4 and D2. The error suggests that all four values are identical.

One way this might happen is if all values are 1 Jan 2013, i.e. 41275, but formatted as m-yy or yy-m.

 

[HongRu]

I suggest you have a close look at what's actually in cells A2:A4 and D2. The error suggests that all four values are identical.

One way this might happen is if all values are 1 Jan 2013, i.e. 41275, but formatted as m-yy or yy-m.

Thanks for your suggestion.
I am sure that they are formatted as "@" not as m-yy or yy-m.
I guess that they are transformed as 13 Jan 2024 by Sumif automatically.

 

[HongRu]

It works for me.

Book1
	A	B	C	D	E
1	Item	Price		Item	Price
2	1-13	1		1-13	1
3	13-1	20
4	13-1	300
Sheet1

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

I'm very curious how you did it.
I have no way to get your result by Sumif.

 

[arthurbr]

I am sure that they are formatted as "@" not as m-yy or yy-m.

To be on the safe side, just format your cell as number to see what it returns

 

[StephenCrump]

I'm very curious how you did it.
I have no way to get your result by Sumif.

If I copy your XL2BB into a blank worksheet (or if I type 1-13 into a cell) my version of Excel interprets this as the date 1 January 2013, i.e. the number 41275.

Similarly, my Excel interprets 13-1 as 13 January in the current year, 2024, i.e. the number 45304.

That's why my SUMIF returns the value 1. It looks like the same for @Phuoc.

We don't actually know what's in your cells (unless your workbook contains no private of sensitive information and you are happy to post a link here, so that we can take a look).

But you can look in more detail at what's really in A2:A4 and D2. For example, In another cell, you could test: =A2="1-13" to see if you really do have a text value in A2.

I guess that they are transformed as 13 Jan 2024 by Sumif automatically.

No, SUMIF isn't transforming anything.

Either the values are identical, or there is some other reason why the calculation isn't updating correctly, e.g. because Calculation is set to manual, or the worksheet contains circular references.

 

[HongRu]

Here is my workbook's link.

My office 2021 excel verion is set language of Traditional Chinese.
And my computer's date is set as formations like 2024/1/13. (Maybe it is why Excel interprets 1-13 as 13 Jan 2024, not as your 1 January 2013)

Every cells (A2:A4, D2) values are prefixed of ' (single quote) in order to transform into TEXT.

I change my pc's date formation as mm/dd/yyyy, and then I have the correct result.

Appreciate.

 

[StephenCrump]

Yes, you'll need to keep all your item numbers as text values, so that Excel doesn't (not very helpfully!) treat some of them as date values.

If your item numbers are text, you shouldn't need to change your date settings.

 

[HongRu]

Yes, you'll need to keep all your item numbers as text values, so that Excel doesn't (not very helpfully!) treat some of them as date values.

If your item numbers are text, you shouldn't need to change your date settings.

I have kept all items as text already, but the sumif still has the wrong result until I change my computer date settings.

By the way, if I use sumproduct instead of sumif, I will get the right result without changing the computer date settings.

Thanks for your reply.