Excel: Sumifs - Multiple Criteria

The criteria expects a single value. Try this formula in cell C5...

    =SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,$H:$H,$B5)

If you want a total for all purple cells, use a simple SUM below the individual sums.

Wayne

Hi

you can put in cell: C4

=SUMPRODUCT(--(F:F=$C$4),--(H:H=B5))

Select allOpen in new window

Try

=SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,H:H,$B5)

here is a sample with the formula, if this is what you are looking for.
ee-sumifs-help-1.xlsx

Hi,

Unfortunately none of these solutions work (Jean, note my original date range - you have changed this to a daily range).

I need to tally results for all the purple code variations.

Mine would work, but none of the dates in column F are within the range specified in cells C4:D4, so it will return 0.

Sorry - I should clarify that I wish for all the purple cells to be tallied in each formula - not just the purple cells in the respective rows.

Doing some further Google searches I came across this formula syntax that may work:

E.g from: Stackoverflow
=SUM(SUMIFS(Quote_Value,Salesman,"JBloggs",Days_To_Close,"<=90",Quote_Month,{"Oct-13","Nov-13","Dec-13"}))

Re-purposing the above formula, I get:

=sum(SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,H:H,{B5,B6,B7,B8,B9}))

But I can't enter this. Any ideas?

I've requested that this question be closed as follows:

Accepted answer: 0 points for dabug80's comment #a40674927

for the following reason:

This works

(not for points)

Doesn't need to be quite that messy:

=SUM(SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,H:H,$B$5:$B$9))
array entered. ;)

Alternative:

In column I, enter formula:
=IFERROR(MATCH(H4,$B$5:$B$9,0),0)

Result will be 0 or a number between 1 & 5, depending on position of H value with B5:B9 range.

Then SUMIFS amend to:
=SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,I:I,">"&0)

Thanks
Rob H

Thanks for the alternatives

Sum(Sumifs()) is not an array formula normally. Please see attachment.
Book1.xlsx

It is if you use a range as criteria, as in this case, rather than an array constant, as in your example.

OK. I'm quite grey on the concepts here. All I know is that my solution worked, so I was happy to use it. But I accept that it may not adhere to standards.

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.