Avatar of dabug80
dabug80
 asked on

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

Avatar of undefined
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))

Open in new window

Saqib Husain

Try

=SUMIFS($G:$G,$F:$F,">="&C$4,$F:$F,"<"&D$4,H:H,$B5)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Wilder1626

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dabug80

ASKER
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?
ASKER CERTIFIED SOLUTION
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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dabug80

ASKER
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. ;)
Your help has saved me hundreds of hours of internet surfing.
fblack61
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

ASKER
Thanks for the alternatives
Hakan Yılmaz

Sum(Sumifs()) is not an array formula normally. Please see attachment.
Book1.xlsx
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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

ASKER
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.
Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes