EXCEL SUMIF Returning Incorrect Values

0
votes

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)

1
excelsumifs
If it is a text string issue, you could just wrap your criteria argument with the VALUE() function... VALUE("831"&Comparison!$A2) – CRUTER

1 Answers

2
votes

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