How about this

=(IF($E30,"Customer billed in CAD",IF($F30,"Customer billed in USD",
IF(ISNUMBER(MATCH($A3,$A$2:$A2,0)),INDEX($D$2:$D2, MATCH($A3,$A$2:$A2,0)),"Customer not billed"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SSJ" wrote in message ...
Hello once again.

The table shown below is an amalgamation of data from two different tables, namely, billing and cost. Therefore, the lines containing the sales value would not have the cost of sales showing on the same line and vice versa.

I need to get a formula for the field 'CATEGORY'. The formula IF(D20,"Customer billed in USD",IF(C20,"Customer billed in CAD","Customer not billed")) works well when considering only the sales fields such as CAD SALES and USD SALES. However, I am not sure what to add in the formula to count the lines with only the cost of sales.

Explanation:
Invoice # 300 for customer ABC against work order # 29775 is clearly a Canadian dollar invoice, so the category will be 'Customer billed in CAD'.
Invoice # 320 for customer EFG against work order # 25000 is clearly a USD invoice, so the category will be 'Customer billed in USD'.
Cost of sales for work order 26000 for customer XYZ should show 'Customer not billed' as there is cost value but no billing as yet.
Cost of sales for work order 30000 for customer PQR should show 'Customer not billed' as there NO cost and NO billing as yet.
Cost of sales for work orders 29775 and 29800 for customer ABC should show 'Customer billed in CAD' because up above customer ABC has been assigned a category due to billing.
Cost of sales for work order 27000 for customer EFG should show 'Customer billed in USD', because up above the customer has been assigned a category due to billing.

In a nut shell, if a customer has been been billed, in which case the formual would assign a category, then all subsequent transactions for that customer should have the same category. And if a customer has only or not cost but no billing, then the category should be "Customer not billed'.

BEFORE
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 100.00
EFG 25000 320 200.00 20.00
XYZ 26000 20.00
PQR 30000
ABC 29775 40.00
EFG 27000 150.00
ABC 29800 40.00


AFTER
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 Customer billed in CAD 100.00
EFG 25000 310 Customer billed in USD 200.00 20.00
XYZ 26000 Customer not billed 20.00
PQR 30000 Customer not billed
ABC 29775 Customer billed in CAD 40.00
EFG 27000 Customer billed in USD 150.00
ABC 29800 Customer billed in CAD 40.00


Thanks in advance
SJ
"SSJ" wrote in message ...
Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ