Help with a formula in Excel

[rogerfoster88]

I am looking for a formula to go into G2 that finds the combined sum in column D that is adjacent to the product in column C, but for the choice of product to be dictated by the product name in F2. So the formula needs to be clever enough to change the volume if the name in F2 changes.
Description		Example 1
	Column C	Column D	Column E	Column F	Column G			Column C	Column D	Column E	Column F	Column G
1	Product	Volume		Product	Total Volume		1	Product	Volume		Product	Total Volume
2	Aaa	9000		Product name here	Formula here		2	Aaa	7000		Aaa	7000
3	Bbb	9000					3	Bbb	9000
4	Bbb	9000					4	Bbb	9000
5	Ccc	9000					5	Ccc	9000
6	Ccc	5000					6	Ccc	5000
Example 2		Example 3
	Column C	Column D	Column E	Column F	Column G			Column C	Column D	Column E	Column F	Column G
1	Product	Volume		Product	Total Volume		1	Product	Volume		Product	Total Volume
2	Aaa	7000		Bbb	18000		2	Aaa	7000		Ccc	14000
3	Bbb	9000					3	Bbb	9000
4	Bbb	9000					4	Bbb	9000
5	Ccc	9000					5	Ccc	9000
6	Ccc	5000					6	Ccc	5000
I have used =Vlookup(F2,C1:G7,2,FALSE) and several iterations of it but I feel like vlookup isn't the answer for my question.
Thanks in advanced for looking into this.

<tbody>
</tbody>

 

[rogerfoster88]

Really appreciate it.

It seems another hurdle has arisen...

I need to divide this formula by 0.5 then again by 12 in the formula itself so i assumed this would work, but it doesn't.

=SUMIF($C$2:$C$8,H2,$D$2:$D$8)+SUMIF($E$2:$E$8,H2,$F$2:$F$8)/0.5,/12)

 

[Fluff]

Maybe
=(SUMIF($C$2:$C$8,H2,$D$2:$D$8)+SUMIF($E$2:$E$8,H2,$F$2:$F$8))/0.5/12

 

[JackDanIce]

Why not divide by 6? Excel uses BIDMAS for operator order in equations. Your first attempt only applies the scalling factor to the second sumif, not both sumif parts.

 

[rogerfoster88]

Hi jack,

I literally just realized this

The real formula is as follows

=SUMIF('[Production Plan.xlsx]Production Plan draft'!$C$8:$C$12,B138,'[Production Plan.xlsx]Production Plan draft'!$D$8:$D$12)/6+SUMIF('[Production Plan.xlsx]Production Plan draft'!$J$8:$J$12,B138,'[Production Plan.xlsx]Production Plan draft'!$K$8:$K$12)/6

This IS WORKING however I can't click and drag to the right and it work for other cells...

Is there a way to have $C$8:$C$12 and $D$8:$D$12 jump to $C$14:$C$18 and $D$14:$D$18, and have $J$8:$J$12 and $K$8:$K$12 to jump to $J$14:$J$18 and $K$14:$K$18 and have them all continue on that pattern for as long as i need it to? Taking the $'s out before the numbers only has the formula change from $C8 to $C9 as you would expect.

 

[Fluff]

Whilst that's beyond my knowledge of formulae, are you dragging to the right, or dragging the formula down?
If dragging to the right I would expect the row numbers to remain the same.

 

[JackDanIce]

Not sure about "jump" but you could add to the formula something that tests the row number via IF and applies the above formula if TRUE?

Easier: Create a helper column and use 1 and 0 to mark rows to include/skip, then filter this column and copy and paste source to visible ranges as required.