CODEWITHSUNDEEP
excelsum
Bob M
Bob M

Reputation: 51

SUMIFS in excel returns 0 when it does not find the value.

I am using a SUMSIF function to count certain values. I am having a problem where if it does not find the values, it returns a 0.

For example. lets say I used a SUMIFS function to sum sales for a company in column B, if factory x and state y were in columns C and D respectively. I am having the problem where if factory G was in column c instead of factory x it will return a 0. This is confusing because some factories may legitimately have 0 sales and it is hard to distinguish. Is there a way to have the SUMIFS function return an "NA" instead of a 0 value in this situation?

I have read other people suggest to use an IFERROR statement, however this does not help because the SUMSIF function does not return an error, but rather just a 0 value.

Thank you in advanced for you help!!

Upvotes: 0

Views: 14145

Answers (3)

KFichter
KFichter

Reputation: 783

I would replace 

=SUMIF

with something like

=IF(COUNTIFS(C:C, "Factory x", D:D, "State y")>0, SUMIFS(B:B,C:C,"Factory x",D:D,"State y"), "NA")

Which would check if the factory exists in column C in the first place.

(Edited from comments below)

Upvotes: 3

User15
User15

Reputation: 304

Nest the SUMIFS in a IF as

=IF(SUMIFS(……………….)<>0, SUMIFS(……………….),NA)

This says if the result is not 0 return result otherwise return NA.

Upvotes: -1

BruceWayne
BruceWayne

Reputation: 23283

You could do something like =If(sumifs([...])=0,"N/A",sumifs([...])

Upvotes: 1

Related Questions