CODEWITHSUNDEEP
excelsumifs
ajc101
ajc101

Reputation: 49

EXCEL SUMIF Returning Incorrect Values

I have an id column on 2 excel sheets. For ex, on one sheet A1 = 831250, A2 = 831251, A3 = 831252. On the other sheet, A1 = 250, A2 = 251, A3 = 252. As you can see, the first sheet has a prefix of 831 in the id column. I am using the formula below, however it is returning the same result for all, which is the total for all 3. Is this a bug, or is there an error in my formula? Weird thing is that VLOOKUP works.

=SUMIF('831 Summary'!$A:$A,"831" & Comparison!$A2,'831 Summary'!$F:$F)

Upvotes: 0

Views: 776

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

"831" & Comparison!$A2 is creating a literal text string. A text string does not equal a number.

Try --("831" & Comparison!$A2) instead. It will convert the text string back to a number.

=SUMIF('831 Summary'!$A:$A,--("831" & Comparison!$A2),'831 Summary'!$F:$F)

Upvotes: 2

Related Questions