Share via

Sumif error in excel formulas

Anonymous
2020-05-19T03:06:38+00:00

Hi All, i have error in SUMIF Formulas, formulas not calculation / not working in this table

Key ID Quantity
1000161100005540 85
1000161100005542 4
1000161100005606 225
1000161100005600 135
1000161100005607 360

And result of SUMIFS is ;

Key ID Result of SUMIF Formula Formulas Manual counting
1000161100005606 720 =SUMIF($B$2:$B$8,D6,$A$2:$A$8) 135
1000161100005607 720 =SUMIF($B$2:$B$8,D7,$A$2:$A$8) 360

Sumif formulas not match with manual counting, please help for this issue. Thank you :)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2020-05-19T05:06:38+00:00

Hello,

Great explanation from Jeeped. As addition, for such a long number (x>15 digits), I would create another colum C, extract ~half of the key_id with RIGHT(B2,5) formula, as it seem to follow pattern 1000161100XXXXX and then use regular SUMIF. 

=SUMIF($C$2:$C$8,RIGHT(D6,5),$A$2:$A$8)

Was this answer helpful?

2 people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2020-05-19T04:19:58+00:00

Your Key IDs are 16 charcters wide. That means that SUMIF/SUMIFS evaluation engine (the thing that lets you use criteria like "<0" or ">="&A2) is trying to convert your 16 character faux numbers into real numbers.

Since you are 1 digit over the 15 digit precision that Excel offers, all of those faux numbers are being treated as the first 15 digits and a zero. e.g. 1000161100005542 is being evaluated as 1000161100005540. Since anything with the same first 15 digits will match, your SUMIF/SUMIFS results are elevated due to the false positives.

For literal interpretation of 16+ digit faux numbers, use SUMPRODUCT. However, it is best to avoid full column references or you will experience calculation lag.

If your quantities are in column A then,

=sumproduct((A$2:INDEX(A:A, MATCH(1e99, A:A)))*(B$2:INDEX(B:B, MATCH(1e99, A:A))=D6))

If your quantities are in column B then,

=sumproduct((B$2:INDEX(B:B, MATCH(1e99, B:B)))*(A$2:INDEX(A:A, MATCH(1e99, B:B))=D6))

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-19T05:35:58+00:00

    Bek_from_Uzbekistan;

    Thanks for the shoutout. Following your logic, the following should be good for faux numbers up to 30 characters in length.

    =SUMIFS(A:A, B:B, D6, B:B, "*"&RIGHT(D6, 15))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-05-19T11:09:42+00:00

    This is great solution! I will definitely save it into my collection, thank you, Jeeped!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-05-19T05:20:28+00:00

    Hello Setiyawan

    I am V. Arya, Independent Advisor, to work with you on this issue. Best to use SUMPRODUCT which will give correct result

    =SUMPRODUCT(($B$2:$B$8=D6)*($A$2:$A$8))

    Was this answer helpful?

    0 comments