How about this

=(IF($E3>0,"Customer billed in CAD",IF($F3>0,"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"))))

 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(D2>0,"Customer billed in USD",IF(C2>0,"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" <jameel68@yahoo.com> wrote in message news:%23W2%230qDBHHA.2328@TK2MSFTNGP02.phx.gbl...
   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(C2>0,"CAD",IF(D2>0,"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