SUMIFS exact match?

[MattKoleczko]

Hi all,

I'm using the following formula to sum from another page =SUMIFS('NTL500'!$M:$M,'GC600'!$C:$C,DATA!$A14,'GC600'!$G:$G,DATA!$B14)

I'm not getting the correct sum value and for the life of me cant figure out why. Does SUMIFS provide an exact cell match or will it pick up everything similar?


Cheers,
Matt

[JieJenn]

Assuming you have two criteria, maybe use SUMPRODUCT. For example, the formula will look like this giving the first parameter is your SUM range.
=SUMPRODUCT($B$2:$B$11*EXACT($A$2:$A$11,E2)*($C$2:$C$11=F2))

[MattKoleczko]

Your formula definitely works but not in my scenario, the data is a report pulled from a program, which has empty lines.
If I add an empty line to your attached spreadsheet its spits the dummy?!

Any ideas?


Matt

[Pete_UK]

SUMIFS is not case-sensitive. Is that what you mean by "exact cell match"?

Pete

[MattKoleczko]

I found my error!

My original SUMIF formula works, it was the fact I had a single duplicate in 500 line items and therefore had double the qty of that item - rookie error!