Help with a formula in Excel

[rogerfoster88]

[TABLE="width: 984"]
<tbody>[TR]
[TD="colspan: 13"]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.[/TD]
[/TR]
[TR]
[TD="colspan: 6"]Description[/TD]
[TD][/TD]
[TD="colspan: 6"]Example 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD][/TD]
[TD][/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Total Volume[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Total Volume[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aaa[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD]Product name here[/TD]
[TD]Formula here[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Aaa[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD]Aaa[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ccc[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Ccc[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ccc[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Ccc[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Example 2[/TD]
[TD][/TD]
[TD="colspan: 6"]Example 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD][/TD]
[TD][/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Total Volume[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Total Volume[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aaa[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD]Bbb[/TD]
[TD]18000[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Aaa[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD]Ccc[/TD]
[TD]14000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Bbb[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ccc[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Ccc[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ccc[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Ccc[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]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.[/TD]
[/TR]
[TR]
[TD="colspan: 13"]Thanks in advanced for looking into this.[/TD]
[/TR]
</tbody>[/TABLE]

 

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