Use a range of cells as criteria in SUMIF

As an example, I have the below formula:

=SUMIF(G1:G25,E1,H1:H25)+SUMIF(G1:G25,E2,H1:H25)+SUMIF(G1:G25,E3,H1:H25)

It is 3 SUMIFs all using the same criteria and sum range, the criteria uses 3 cells all in the same range.

I want to be able to condense this formula to something like the below:

=SUMIF(G1:G25,E1:E3,H1:H25)

where the criteria is the range of cells. I have tried:

{=SUMIF(G1:G25,E1:E3,H1:H25)}

=SUMIF(G1:G25,{E1:E3},H1:H25)

=SUM(SUMIF(G1:G25,{E1:E3},H1:H25))

Is there a way to achieve this ? perhaps even with SUMPRODUCT ?

Also, in place of the range E1:E3 I would like to use a named range, if possible, If not just a way of condensing the multiple SUMIFs will do for me.

PeterH

Posted 2019-04-16T07:56:16.487

Reputation: 5 346

Answers

The smallest possible Formula I would like to suggest is:

=SUMPRODUCT(ISNUMBER(MATCH(A1:A7,A9:A11,0))*B1:B7)

Your Formula should be re-written like shown below:

=SUMPRODUCT(ISNUMBER(MATCH(G1:G25,E1:E3,0))*H1:H25)

You may adjust cell references in the Formula as needed.

Rajesh S

Posted 2019-04-16T07:56:16.487

Reputation: 6 800

You seem to have succeeded where I didn't so I deleted my wrong answer. You condensed the OP's formula a fair bit using SUPRODUCT, ISNUMBER and MATCH, and being there is the range E1:E3 used, a named range can be used in its place. – Chris Rogers – 2019-04-16T11:44:43.400

1I knew there would be a way using SUMPRODUCT, thanks Raj – PeterH – 2019-04-16T13:43:52.170

@PeterH,, glad to help you.. please keep asking ☺ – Rajesh S – 2019-04-17T04:33:28.063

You can use SUMPRODUCT(SUMIFS())

=SUMPRODUCT(SUMIF(A:A,D1:D3,B:B))

The SUMPRODUCT forces the iteration of the Criteria. The others can be full column without detriment. It is basically doing 3 SUMIF()s and adding the results.

FYI: You can also do with SUM: =SUM(SUMIF(A:A,D1:D3,B:B)) as long as you Array enter with Ctrl-Shift-Enter instead of Enter.

Scott Craner

Posted 2019-04-16T07:56:16.487

Reputation: 16 128

I was closes with =SUM(SUMIF(G1:G25,{E1:E3},H1:H25)) I put the criteria in curly brackets, rather than the whole formula, thanks for the answer, this is very useful as I am assuming it will also work with SUMIFS also – PeterH – 2019-04-16T14:53:09.493

@PeterH yes it will work with SUMIFS,COUNTIF, and COUNTIFS. – Scott Craner – 2019-04-16T15:01:36.127