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

I have the attached spreadsheet with a Sumifs formula:

=SUMIFS($G:$G,$F:$F,">="&C

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

Cheers.

ee-sumifs-help.xlsx

Microsoft Excel

Hi

you can put in cell: C4

you can put in cell: C4

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

Try

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

=SUMIFS($G:$G,$F:$F,">="&C

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.

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?

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

E.g from: Stackoverflow

=SUM(SUMIFS(Quote_Value,Sa

Re-purposing the above formula, I get:

=sum(SUMIFS($G:$G,$F:$F,">

But I can't enter this. Any ideas?

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.

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

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. ;)

Doesn't need to be quite that messy:

=SUM(SUMIFS($G:$G,$F:$F,">

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

In column I, enter formula:

=IFERROR(MATCH(H4,$B$5:$B$

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

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.

=SUMIFS($G:$G,$F:$F,">="&C

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

Wayne