Combined SUMIFs

**Mac Johnson** · 03-19-2009, 04:40 AM

I want the SUMIF statement to consider the "text" in two cells and then SUMIF if the two cells meet the criteria eg as below but it does not work.#

=SUMIF(C20:C50,"PM",E20:E50)+(D20:D50,"BG",E20:E50)

**DonkeyOte** · 03-19-2009, 04:47 AM

Please PM a Mod and ask for your thread to be moved to the appropriate Forum - ie Worksheet Functions

re: your formula if you're saying that both C must equal PM and D BG before summing E then:

=SUMPRODUCT(--(C20:C50="PM"),--(D20:D50="BG"),E20:E50)

or create a concatenation of C:D say in F

F20: =C20&":"&D20
copied down to F50

Then you can dispense with Array (Sumproduct) and use a standard SUMIF

=SUMIF(F20:F50,"PM:BG",E20:E50)

I would generally advise the Concatenation approach myself over Array.

**Mac Johnson** · 03-19-2009, 06:07 AM

DonkeyOte,
I have tried the SUMPRODUCT version and I intend to use that, I am trying to prevent the use of additional columns etc, but your point has been noted. Maybe when I am a bit more conversant with trying to manipulate data I will understand why it would be better to do it your preferred way.

Thanks,
Mac

**DonkeyOte** · 03-19-2009, 06:11 AM

Originally Posted by Mac Johnson

Maybe when I am a bit more conversant with trying to manipulate data I will understand why it would be better to do it your preferred way.

Arrays are "more expensive" in terms of performance than non-arrays and thus if overused they will affect the overall performance of your model (significantly so pending volume of arrays and ranges involved)... elegance & efficiency do not always go hand in hand in XL unfortunately.