My eyes Are Tiered, Forehead Bruised, My Desk Dented.!! Help???

K

KG121953

Every time I think I have ........I DON'T!!!
Example;
Lets say I have 2 Drop-Down Boxes D1 D2
D1 has Product listed. (Example; Apples, Pears, Peach’s, Duck Soup…..)
D2 has suppliers listed. (Example; Orchard A, B, C, Dick’s, Jane’s, Spot’s)
D3 is where I enter the amount of product I need (1lbs 2lbs 5gals ...)
G2 is "Total" amount "$1.00's "for all Apples, Pears, Peach’s, Duck Soup
that I need to purchase from all suppliers.
I have a price list of each, Orchard A (R10:R16), B (T10:T16), and so on
So let’s say Duck Soup at Jane’s cost $1.00 a Gallon, and I need 10 Gallons
D3=10
Z10 = $1:00 (Jane's pric list Z10:Z16)
Y3 =IF(AND(D1="Duck Soup",D2="Jane's"),Z10,0)
So in this case G2=Y3*D3 (G2=$10.00)
What I would like to do is keep a running Total for every thing I need to
purchase in G2. Since D1 and D2 change for every product and supplier
Can this be done with the changing variables that I have?
Or do I have to have a separate Data entry line for every product and
supplier?
I have over 50 products and 20 suppliers….PLEASE HELP
Aspirin, Tylenol, and Advil running low!!!!
Thank You
 
D

Don Guillett

So, if you had a layout of
product supplier price each
1 a .10
2 b .20
3 c .25
=SUMPRODUCT((A2:A22=1)*(B2:B22="a")*C2:C22)*10 (where 10 is the units
desired)
=SUMPRODUCT((A2:A22=a1)*(B2:B22=b1)*C2:C22)*c1 (where c1 is the units
desired)
 
Top