Help with a %mix formula please

[AlanN]

I have a spreadsheet where I need to calculate the % mix of a product that is based on what location AND PdWk it belongs to.
For example, Prod1 in Loc1 for PD06Wk3 is =50/sum($d$2:$d$5)

Can anyone provide a formula that doesn't require the user to determine the range for ech PdWk and Loc is?


PdWk Location Prod Qty Loc%MixForPdWk
Pd06Wk3 Loc1 Prod1 50
Pd06Wk3 Loc1 Prod2 51
Pd06Wk3 Loc1 Prod3 52
Pd06Wk3 Loc1 Prod4 53
Pd06Wk4 Loc1 Prod1 25
Pd06Wk4 Loc1 Prod2 26
Pd06Wk4 Loc1 Prod3 27
Pd06Wk4 Loc1 Prod4 28
Pd06Wk3 Loc2 Prod1 10
Pd06Wk3 Loc2 Prod2 11
Pd06Wk3 Loc2 Prod3 12
Pd06Wk3 Loc2 Prod4 13
Pd06Wk4 Loc2 Prod1 30
Pd06Wk4 Loc2 Prod2 31
Pd06Wk4 Loc2 Prod3 32
Pd06Wk4 Loc2 Prod4 33


TIA, Alan N

 

[Bob Phillips]

Alan,

Assuming they are all unique, you could try something like
=SUMPRODUCT((A1:A20="Pd06Wk3")*(B1:B20="Loc1")*(C1:C20="Prod1"),(D120))

Just adjust to your ranges. The strings can be replaced by cells to be testyed against of course.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


I have a spreadsheet where I need to calculate the % mix of a product that is based on what location AND PdWk it belongs to.
For example, Prod1 in Loc1 for PD06Wk3 is =50/sum($d$2:$d$5)

Can anyone provide a formula that doesn't require the user to determine the range for ech PdWk and Loc is?


PdWk Location Prod Qty Loc%MixForPdWk
Pd06Wk3 Loc1 Prod1 50
Pd06Wk3 Loc1 Prod2 51
Pd06Wk3 Loc1 Prod3 52
Pd06Wk3 Loc1 Prod4 53
Pd06Wk4 Loc1 Prod1 25
Pd06Wk4 Loc1 Prod2 26
Pd06Wk4 Loc1 Prod3 27
Pd06Wk4 Loc1 Prod4 28
Pd06Wk3 Loc2 Prod1 10
Pd06Wk3 Loc2 Prod2 11
Pd06Wk3 Loc2 Prod3 12
Pd06Wk3 Loc2 Prod4 13
Pd06Wk4 Loc2 Prod1 30
Pd06Wk4 Loc2 Prod2 31
Pd06Wk4 Loc2 Prod3 32
Pd06Wk4 Loc2 Prod4 33


TIA, Alan N