Combining or Optimizing Multiple SUMPRODUCTS

**skyxliner** · 12-23-2010, 06:07 AM

I have a cell that requires the SUM of 5 SUMPRODUCTS.

The 5 SUMPRODUCTS are checking for criterias in the same column (ex. A) and summing the quantities in the same column (B).

It's currently setup like this,
[criteria 1]*[sum criteria 1]
[criteria 2]*[sum criteria 2]
[criteria 3]*[sum criteria 3]
[criteria 4]*[sum criteria 4]
[criteria 5]*[sum criteria 5]

Since it's summing the quantity in the same column, in theory, couldn't I combine it?

[criteria1]*[criteria2]*[criteria 3]*[criteria 4]*[criteria 5]*[sum quantity column B]?

can anyone help me with the formula?

I'm trying to optimize this so it's faster calculation in excel.

**zbor** · 12-23-2010, 06:10 AM

Can you upload excel example with inputs and desired output?

**skyxliner** · 12-23-2010, 06:20 AM

this is a sample of what i have right now, i was thinking in theory since all the sumproducts are summing all the same column but just along diff criterias, there should be a way to shorten this?

**DonkeyOte** · 12-23-2010, 06:23 AM

It sounds like an OR test which are in general terms conducted by means of addition in SUMPRODUCT/Arrays

Please Login or Register  to view this content.

If the tests are not mutually exclusive (ie for any given iteration >1 TRUE results) and where in such scenarios the sum_range should be aggregated only once - encase the addition of the tests within a SIGN call (will return 0 [0 TRUE] or 1 [1+ TRUE]).

It may be the case that you can avoid addition with an alternative method but as zbor states an example would help.

**DonkeyOte** · 12-23-2010, 06:24 AM

Given the sample file and singular condition you should be using SUMIF - not SUMPRODUCT:

Please Login or Register  to view this content.

In SUMPRODUCT terms:

Please Login or Register  to view this content.

but to reiterate you should be using SUMIF.

**skyxliner** · 12-23-2010, 06:41 AM

WOW I didn't realize the sumproduct was overkill for what I wanted to achieve. I heard about how sumproducts were calculation intensive as well.

I will be using this formula multiple times throughout the worksheet, can you confirm that this is the order of fastest to slowest calculations for excel?

1st = SUM(SUMIF(A2:A14,{"a","b","c","d","e"},B2:B14))
2nd = SUMPRODUCT(--ISNUMBER(MATCH(A2:A14,{"a","b","c","d","e"},0)),B2:B14)
3rd = =SUMPRODUCT((B2:B14)*(A2:A14="a"))+SUMPRODUCT((B2:B14)*(A2:A14="b"))+SUMPRODUCT((B2:B14)*(A2:A14="c"))+SUMPRODUCT((B2:B14)*(A2:A14="d"))+SUMPRODUCT((B2:B14)*(A2:A14="e"))

thanks so much, you've been very helpful. I'm expecting my calculations to be even faster now.

**DonkeyOte** · 12-23-2010, 09:23 AM

SUMPRODUCT and Arrays are inefficient and should (IMO) be used in moderation esp. with large precedent ranges.

Given the singular condition SUMIF is the way forward - you would not use SUMPRODUCT.

In terms of the SUMPRODUCTs - if only by means of explanation - the 2nd you list is not an approach you would use.

For an OR based test (as outlined) you would use a single SUMPRODUCT:

Please Login or Register  to view this content.

(here the tests are mutually exclusive so no need for SIGN test etc...)

The ISNUMBER/MATCH approach is more succinct - more so as the number of options increase.

Another alternative to the above would be:

Please Login or Register  to view this content.

however here we're explicitly coercing underlying values so should any cell within B2:B14 contain a non-numeric entry (eg Null or "apple" etc...) then a #VALUE! error would result (the alternatives would not).

**skyxliner** · 12-23-2010, 10:13 PM

thanks so much for spending your time for such a detailed answer. much helped.

**skyxliner** · 12-25-2010, 04:48 PM

One more question,

SUMPRODUCT(--ISNUMBER(MATCH(A2:A14,{"a","b","c","d","e"},0)),B2:B14)

If I don't want to reference A,b,c,d,e

how can i reference cells

SUMPRODUCT(--ISNUMBER(MATCH(A2:A14,(A1,A2,A3,A4,A5),0)),B2:B14) ???

**daddylonglegs** · 12-25-2010, 04:52 PM

If the cells are a contiguous range like A1:A5 then you can use

SUMPRODUCT(--ISNUMBER(MATCH(A2:A14,A1:A5,0)),B2:B14)

...or with SUMIF....

=SUMPRODUCT(SUMIF(A2:A14,A1:A5,B2:B14))

**skyxliner** · 12-25-2010, 04:56 PM

i'm sorry I copied paste the wrong formula,

I meant if I wanted to use SUMIF

SUM(SUMIF(A2:A14,{"a","b","c","d","e"},B2:B14))

how can i reference cells instead, because it's not working out how I want it

SUM(SUMIF(A2:A14,(A2,A3,A4,A5),B2:B14))

it doesn't seem to work as the sum comes out wrong.

**skyxliner** · 12-25-2010, 05:10 PM

=SUMPRODUCT(SUMIF(A2:A14,A1:A5,B2:B14))

that works perfect, but what if i dont' want to reference ranges but specific cells

=SUMPRODUCT(SUMIF(A2:A14,(A1, B3),B2:B14))

comes out with the wrong sum, from my understanding, SUMIF only works on ranges of data and not specified cells

**skyxliner** · 12-25-2010, 05:19 PM

Right now i'm doing this and it sums up correctly

=SUMIF(sales_item,promos!A4,sales_quantity)+SUMPRODUCT(SUMIF(sales_item,promos!A9:A15,sales_quantity))

Is there a more efficeint formula?

**DonkeyOte** · 12-26-2010, 03:49 PM

Not really - SUMIF as you now know does not cater for non-contiguous criteria ranges.

**ExcelStefan** · 07-02-2012, 09:48 AM

Originally Posted by DonkeyOte

Please Login or Register  to view this content.

Nice!!

This also works for COUNTIF

Please Login or Register  to view this content.

Combining or Optimizing Multiple SUMPRODUCTS

LinkBack

Thread Tools

Rate This Thread

Display

Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Re: Combining or Optimizing Multiple SUMPRODUCTS

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions