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...

	A​	B​	C​	D​
1​		Price	Mic
2​	Prod A	$5.00​	50.00%​	4.195​
3​	Prod B	$3.99​	20.00%​
4​	Prod C	$2.99​	30.00%​

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...

	I​	J​	K​
1​	Product A
2​	Price	$5.00​	4.195​
3​	Mix	50.00%​
4​
5​	Product B
6​	Price	$3.99​
7​	Mix	20.00%​
8​
9​	Product C
10​	Price	$2.99​
11​	Mix	30.00%​

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!!