Excel - SUMIF - Do not return correct value

0
votes

I am facing a problem with values of...

What I want

I want the SUM of each UNIQUE key from Other sheet,

I two sheets:

Sheet 1 - With Raw Data

KEY VALUE
abc 123

Sheet 2 - Unique Key (where I want to Return the total sum for each unique key)

KEY (unique) Total VALUE (sum) for each key
abc Total Value

I have used the this formula: =SUMIF(Sheet1!A:A;A2;Sheet1!B:B)

Using Google Sheets I have the correct value. But, I do not know why excel returns different values.

Excel SUMIF vs Excel Pivot Tables Vs Google Sheets:

I have checked by THE SUM of Column VALUE trying:

  • SUMIF
  • and PIVOT TABLES

Outputs:

  • Google Sheets Sum If: 7.686.075,96
  • Excel Using Pivot Tables: 46.909.880,24
  • Excel Using Sum If Function: 7.686.075,96

Excel and Google Sheets have different Outputs Using the same data, and the same formulas, Google sheets returns the correct value.

Excel SumIF and Excel Pivot Tables have different Outputs Using the same data Excel Pivot Tables returns the correct value.

Link For Excel Sheet

Link for Google Sheet

Can anyone give a help here?

Thanks !!!!

2
excelexcel-formulasumifs
Have you considered formatting the unique key values as text? I'm not 100% sure it will fix it but worth a try perhaps? – Spencer Barnes

2 Answers

1
votes

It's because your keys are long numeric strings and SUMIF/COUNTIF will try to convert those to numbers but, because Excel only works with 15 significant figures in numeric data, you get a lot more matches for each item. You could use:

=SUMPRODUCT(--(DATA!$A$3:$A$7996=A3),DATA!$B$3:$B$7996)

instead.

0
votes

Another solution to the problem mentioned by @Rory is to force SUMIF to see that value as text: (IF your keys are all the same length)

=SUMIF(DATA!A:A,'SUM IF'!A3&"*",DATA!B:B)

or

=SUMIF(DATA!A:A,"*"&'SUM IF'!A3,DATA!B:B)