Sumproduct in a vertical column

[Jwood]

I know there's an easy way to do this, but I can't figure it out. I need sumproduct to work on two different data sets vertically. I usually do them where the criteria and data are in separate columns.
I've put a simplified example below. I'm getting tired of doing the formula I've been using because I'm always adding rows to it and i have to manually add them each time.


Column A Column B
1 Product A
2 Price $5.00
3 Mix 50.0%
4
5 Product B
6 Price $3.99
7 Mix 20.0%
8
9 Product C
10 Price $2.99
11 Mix 30.0%

$4.20

formula =(B2*B3)+(B6*B7)+(B10*B11)+ (blah * blah) + (yuck * yuck)+(this s*cks * this s*cks)

 

[FDibbins]

If you considered restricting your data, this wuld be much easier...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Price[/td][td]Mic[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Prod A[/td][td]

$5.00​

[/td][td]

50.00%​

[/td][td]

4.195​

[/td][/tr]

[tr][td]

3​

[/td][td]Prod B[/td][td]

$3.99​

[/td][td]

20.00%​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Prod C[/td][td]

$2.99​

[/td][td]

30.00%​

[/td][td][/td][/tr]
[/table]
D2=SUMPRODUCT(B2:B4*C2:C4)

 

[Jwood]

Yes, but it's an existing file that would be too much work to restructure. I would have to rework the entire model if I did that. I know there's a way to do it, I just can't find it.

 

[FDibbins]

Maybe in the short term.

Anyway, try this...
[Table="width:, class:grid"][tr][td] [/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td]Product A[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Price[/td][td]

$5.00​

[/td][td]

4.195​

[/td][/tr]

[tr][td]

3​

[/td][td]Mix[/td][td]

50.00%​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Product B[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Price[/td][td]

$3.99​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Mix[/td][td]

20.00%​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Product C[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Price[/td][td]

$2.99​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Mix[/td][td]

30.00%​

[/td][td][/td][/tr]
[/table]
K2=SUMPRODUCT(J2:J10*J3:J11)
Note the range offsets

 

[Jwood]

Thanks FDibbins. What you have would work perfect. However, I should have specified there is more data on rows under each product than just price and mix. There's costs, costs %, etc., I guess I simplified my example too much.
Need something that would specify to look at Price and Mix. Sorry, that's my bad. I wasn't thinking it would be as simple as what you said so I didn't specify much more than that.

 

[wideboydixon]

How about:

=SUMPRODUCT((I2:I10="Price")*(I3:I11="Mix"),(J2:J10)*(J3:J11))

WBD

 

[Jwood]

This worked! Thanks!!