Combining or Optimizing Multiple SUMPRODUCTS

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.

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?

Originally Posted by DonkeyOte

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

Nice!!

This also works for COUNTIF

=SUM(COUNTIF(A2:A14,{"a","b","c","d","e"}))

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.

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

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) ???

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.

=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

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?