Sumproduct, array, if to multiply mixed series of numbers

[jg70124]

I have a long column of numbers like this:

A B
1 Sales 123
2 GM% 35%
3 Other 234
4 Other 345
5 Sales 123
6 GM% 35%
7 Other 234
8 Other 345
9 Sales 123
10 GM% 35%
11 Other 234
12 Other 345
13 Sales 123
14 GM% 35%
15 Other 234
16 Other 345

and so on.

Is there a formula I can use that does a sumproduct of all the sales
numbers and all the GM%s, and ignores the rest?

Thanks

 

[T. Valko]

Try this:

=SUM(SUMIF(A1:A16,{"Sales","GM%"},B1:B16))

 

[Don Guillett]

=SUMPRODUCT((a1:a24={"GM%","sales"})*b1:b24)

 

[jg70124]

Thanks.

That gives the sum of all "Sales" + sum of all "GM%". Any way to
modify so that it gives the sumproduct?

That is, I want a formula that gives (sales*GM% + sales*GM% + sales*GM
%....)

 

[Charles Williams]

Assuming that Sales is always immediately followed by GM% then try this

=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales"))

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[jg70124]

That's the ticket - thanks.

Assuming that Sales is always immediately followed by GM% then try this

=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales"))

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Grouphttp://www.decisionmodels.com/OZEUC.htm

 

[T. Valko]

I guess I misunderstood your requirement.

 

[Bernd P]

Hello,

And maybe you would like to add a plausibility checks like
=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales"),--($A$2:$A
$16="GM%"))
=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$2:$A$16="GM%"))

If this does not show identical results then your data has some flaw
in it...(unless these formulas are wrong

Regards,
Bernd

 

[jg70124]

Hello,

And maybe you would like to add a plausibility checks like
=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales"),--($A$2:$A
$16="GM%"))
=SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$2:$A$16="GM%"))

If this does not show identical results then your data has some flaw
in it...(unless these formulas are wrong

Regards,
Bernd

Perfect. Thanks.