Combining Two SUMIFS

**Craig K.** · 05-29-2013, 10:08 AM

I have this fun formula

Formula:

Please Login or Register  to view this content.

and was wondering if there is a way to combine the part of it that says

Formula:

Please Login or Register  to view this content.

into one SUMIFS formula?

Nothing critical, as the formula works as-is, just curious.

**Pepe Le Mokko** · 05-29-2013, 11:47 AM

Perhaps

Please Login or Register  to view this content.

?

**Jonmo1** · 05-29-2013, 11:52 AM

Try replacing that part with

SUMPRODUCT((Data!$G:$G=E$6)*Data!$J:$K)

**Craig K.** · 05-29-2013, 04:34 PM

Neither of those are working for me. Thanks for your input though.

**Jonmo1** · 05-29-2013, 04:45 PM

First let me just say....
"If it ain't broke, don't fix it"
and
"Shorter does not = Better"

Anyway, that said..

Originally Posted by Craig K.

Neither of those are working for me. Thanks for your input though.

In what way does it not work?
Do you get an error? What error?
Does it not give the correct result? What result DID it give? What result did you expect, and why?

Assuming this works
=IFERROR(SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/(SUMIFS(Data!$J:$J,Data!$G:$G,E$6)+SUMIFS(Data!$K:$K,Data!$G:$G,E$6)),0)
Strip of the Iferror just to make it simpler for now
=SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/(SUMIFS(Data!$J:$J,Data!$G:$G,E$6)+SUMIFS(Data!$K:$K,Data!$G:$G,E$6))

You want to replace the part AFTER the / with something that combines those 2 parts into 1 right?
(SUMIFS(Data!$J:$J,Data!$G:$G,E$6)+SUMIFS(Data!$K:$K,Data!$G:$G,E$6))

This should accomplish the same thing
SUMPRODUCT((Data!$G:$G=E$6)*Data!$J:$K)

So
=SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/(SUMIFS(Data!$J:$J,Data!$G:$G,E$6)+SUMIFS(Data!$K:$K,Data!$G:$G,E$6))
becomes
=SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/SUMPRODUCT((Data!$G:$G=E$6)*Data!$J:$K)

Put the IFERROR part back in

=IFERROR(SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/SUMPRODUCT((Data!$G:$G=E$6)*Data!$J:$K),0)

**Jonmo1** · 05-29-2013, 04:48 PM

A possible reason for this to not work is if there are NON numeric values in column J or K
Sumif can handle that, sumproduct can't.

**daddylonglegs** · 05-29-2013, 05:00 PM

You could revert to an "array formula", i.e. this version

=IFERROR(SUMIFS(Data!$AM:$AM,Data!$G:$G,E$6)/SUM(IF(Data!$G:$G=E$6,Data!$J:$K)),0)

confirmed with CTRL+SHIFT+ENTER

That will allow non-numeric values in Data columns J and K......

......but it may be better to stick with your original as SUMIFS or SUMIF is generally more efficient

**Craig K.** · 05-29-2013, 05:29 PM

Originally Posted by Jonmo1

A possible reason for this to not work is if there are NON numeric values in column J or K
Sumif can handle that, sumproduct can't.

You're right, I have column headers. I adjusted the formula you had to SUMPRODUCT((Data!$G2:$G1000=E$6)*Data!$J2:$K1000) and it worked. Bad etiquette on my part for not explaining what was happening when it didn't work. Cheers!

@longlegs - In general I try to stay away from array formulas because I don't who I'm going to be handing my spreadsheets off to. Also, if I revisit an old spreadsheet I made and there are array formulas, I probably won't notice the {} signifying it.