Multiple conditions in one column for sumproduct.

**einarai** · 08-07-2011, 02:07 AM

Hi guys,

I've been trying to enter multiple conditions in sumproduct formula for one column (A3:A3000). This one works, but is there a way to shorten it?

(SUMPRODUCT(--(DXB!A3:A3000="PM"),--(DXB!C3:C3000=1), DXB!G3:G3000))+(SUMPRODUCT(--(DXB!A3:A3000="NM"),--(DXB!C3:C3000=1), DXB!G3:G3000))

I essentially added two different sumproduct formulae here, but all i need for it to do is recognize both "PM" and "NM" in A3:A3000 column. A3:A3000={"PM,"NM"} doesnt work, tried.

**DonkeyOte** · 08-07-2011, 02:28 AM

If as implied you are using XL2007 then you should be using SUMIFS rather than SUMPRODUCT - the latter is inefficient:

=SUM(SUMIFS(DXB!G3:G3000,DXB!A3:A3000,{"PM","NM"},DXB!C3:C3000,1))

In SUMPRODUCT terms, either:

=SUMPRODUCT(--ISNUMBER(MATCH(DXB!A3:A3000,{"PM","NM"},0)),--(DXB!C3:C3000=1),DXB!G3:G3000)

or

=SUMPRODUCT((DXB!A3:A3000="PM")+(DXB!A3:A3000="NM"),--(DXB!C3:C3000=1),DXB!G3:G3000)

or

=SUMPRODUCT((DXB!A3:A3000={"PM","NM"})*(DXB!C3:C3000=1)*DXB!G3:G3000)

I prefer the first option in terms of flexibility.

**einarai** · 08-07-2011, 03:25 AM

Thanks! This was brilliant, I wasnt aware of SUMIFS before.

A quick follow-up question, is it possible (and how) to expand sum_range to more than one column? For instance, in

=SUM(SUMIFS(DXB!G3:G3000,DXB!A3:A3000,{"PM","NM"},DXB!C3:C3000,1))

can I use sum_ranges of like G3:H3000, I tried just typing that in and I get #VALUE error. I basically want it to calculate a sum of given items in two columns, not one.

**DonkeyOte** · 08-07-2011, 03:35 AM

For SUMIFS the dimensions of each range must be identical.

Here it seems you have 2 criteria columns which are 1 column wide therefore your summation range must also be 1 column wide.
You would need to create 2 separate SUMIFS - one for each column of the summation range - this is not ideal obviously
(in fact you would be running 4 SUMIFS - PM x 2, NM x 2)

You may then choose to revert to SUMPRODUCT, perhaps along the lines of:

=SUMPRODUCT(ISNUMBER(MATCH(DXB!A3:A3000,{"PM","NM"},0))*(DXB!C3:C3000=1)*{1,1},DXB!G3:H3000)

the above would handle non-numerics in G3:H3000 (avoids explicit coercion of summation range)

else consider adding a total column which sums G:H and use that in the SUMIFS.