Help with a %mix formula please

A

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
 
B

Bob Phillips

Alan,

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help me with a formula 4
help with a formula 2
Help with a formula please 2
Sorting with a macro or a formula 8
Need help with a Formula 4
Help with taking formula to vba code 5
Totally stuck trying to format,loop etc...please help a novice!tha 20
Importing Long String - String Manipulation (EDI EANCOM 96a) 6

Top