sumifs returning 0 value

Question

I am using SUMIFS to total values in a range based on two different criteria. The formula I am using is:

=SUMIFS(daily!B200:B226,'revised daily'!B200:B226,"CF",'revised daily'!B200:B226,"2 CF") 

The answer should be 15, as there are 13 "1" that correspond to CF and 1 "2" that corresponds to 2 CF (13+2=15).

When either criteria "CF" or "2 CF" is used individually, the correct result (either 13 or 2, respectively) displays in the dialog box as I'm building the formula. However, when I put both criteria in (as shown above), the result is 0.

Can anyone help with a potential solution?

Answer 1

SUMIFS sums the values for which ALL conditions are met, not the values for which ANY of the conditions is met.

So you should use

=SUMIFS(daily!B200:B226,'revised daily'!B200:B226,"CF") + SUMIFS(daily!B200:B226,'revised daily'!B200:B226,"2 CF")

Answer 2

Mary AnnPoatsey,

on the offhand chance that you want to sum any row that contains CF  (if the row needs to be summed, it will contain CF or 2 CF and rows that won't be summed do not contain the substring CF in that column. 

Then you could do:

=SUMIFS(daily!B200:B226,'revised daily'!B200:B226,"*CF")

or using the original single condition version (SUMIF without the added last S)

=SUMIF('revised daily'!B200:B226,"*CF",daily!B200:B226)

Where the "*" is seen as a wild card character and the condition is essentially that the entry ends in CF.  

--

Regards,

Tom Ogilvy

Answer 3

You will have to use 2 SUMIF formulas added together (or SUMPRODUCT formula)

=SUMIF('revised daily'!B200:B226,"CF",daily!B200:B226)+SUMIF('revised daily'!B200:B226,"2 CF",daily!B200:B226)

This is because the criteria in a SUMIFS is "AND" not "OR" so you can't say where my value is "CF" AND it is "2 CF"

Answer 4

Alternative to using multiple SUMIF, you may use below:

=SUMPRODUCT((daily!$B$200:$B$226)*(NOT(ISNA(MATCH('revised daily'!$B$200:$B$226,{"CF","2 CF"},0)))))

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/SumIfs_ArrayCriteria_1.xlsx

Regards,

Amit Tandon