Excel 2003, need formula that meets two conditions then divides sums of numbers

J

J.D.

I have named ranges for CustID, CustType, Tons, and NetSales, sorted
alphabetically by CustID. I need to find "PerTon" for each customer
(the sum of NetSales divided by the sum of Tons). I need an array
formula that gives me the answer to: lf Y2 = CustID And X2 = CustType,
Sum(NetSales)/Sum(Tons). For the data below, the PerTon for customer
AOZ should be $30.97, but I can't come up with an array formula that
gives me the correct answer. I've been working on this for 2 days now,
unsuccessfully. Please, I'd appreciate any help I can get.

A B C D X2 Y2
CustID CustType Tons NetSales CustID CustType
1 AOZ BOX 500 25,000.00 AOZ Box
2 AOZ BOX 998 30,000.00 AUZ Box
3 AOZ BOX 850 22,000.00 ASZ TRADE
4 AOZ BOX 655 16,000.00 BMZ EXPORT
5 AUZ TRADE 42 14,845.00
6 ASZ EXPORT 61 20,293.00
 
A

Aladin Akyurek

Since you are on Excel 2003, convert the data area in A:D into a List by
means of Data|List|Create List and enjoy the benefits of the list
functionality.

Z2:

=SUMPRODUCT(--(CustID=X2),--(CustType=Y2),NetSales)/SUMPRODUCT(--(CustID=X2),--(CustType=Y2),Tons)
 
J

J.D.

Thank you, thank you, thank you!!!

Aladin said:
Since you are on Excel 2003, convert the data area in A:D into a List by
means of Data|List|Create List and enjoy the benefits of the list
functionality.

Z2:

=SUMPRODUCT(--(CustID=X2),--(CustType=Y2),NetSales)/SUMPRODUCT(--(CustID=X2),--(CustType=Y2),Tons)
Click to expand...
 
Top