 # Excel: Sumifs - Multiple Criteria

Hello,

I have the attached spreadsheet with a Sumifs formula:

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

I would like the formula to work for the multiple criteria portion 'B5:B9'. Is this possible?

Cheers.

ee-sumifs-help.xlsx
Microsoft Excel Last Comment
Martin Liss

8/22/2022 - Mon
Wayne Taylor (webtubbs)

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
Wilder1626

Hi

you can put in cell: C4

``````=SUMPRODUCT(--(F:F=\$C\$4),--(H:H=B5))
``````
Saqib Husain

Try

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

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

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.
Wayne Taylor (webtubbs)

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.
dabug80

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?
dabug80

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
dabug80

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
Rory Archibald

(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. ;)
Rob Henson

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
dabug80

Thanks for the alternatives
Hakan Yılmaz

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

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