Combine four Conditions in Excel

[lovemachine]

HELP!!!

I would like to combine the four formula below in one cell (Column D):

=IF(AND(C4="Electrical",A4="Credit"),$K$4,$K$3) Notes: If the warranty category is Electrical and the account type is Credit, return a rate of 5% if TRUE and 10% if FALSE.​

=IF(C4="Furniture",A4="Credit"),$K$6,$K$5) Notes: If the warranty category is Furniture and the account type is Credit, return a rate of $500 if TRUE and $1,000 if FALSE.​

=IF(C4="Optical"),$K$7) Notes: If the warranty category is Optical, return a rate of $500 if TRUE.​

=IF(AND(A4="POS",C4="Furniture"OR"Electrical"),$K$8,0) - might be wrong??!! Notes: If the account type is POS and the warranty category is Electrical, return the "Cash Electrical value" of 10% and if the warranty category is Furniture, return the "Cash Furniture value of $1000.​

 

[KenU]

If you will make a slight modification to your rate table as shown in the image, you can use the following formula:

=SUMPRODUCT(($I$3:$I$8=C2)*($J$3:$J$8=IF(A2="POS","Cash",A2)),$K$3:$K$8)

Note that the percentages are displayed in general format so that 5% = 0.05 in column D. I also added rows 26 and 27 to test the POS case.

Hope that helps.

Regards,
Ken

 

[lovemachine]

Thanks. Resolved.