Search code examples
excelexcel-formulasumifs

Excel - SUMIF - Do not return correct value

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 !!!!

Solution

  • 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.