CODEWITHSUNDEEP
excelexcel-formulasumifs
Andre Nevares
Andre Nevares

Reputation: 741

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:

Outputs:

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

Upvotes: 0

Views: 1331

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

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)

Upvotes: 0

Rory
Rory

Reputation: 34075

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.

Upvotes: 1

Related Questions