Multiple IFs and other options

[Ogunhe]

Hello

I am trying to get a fairly tedious pricing structure sorted with a formula, and could do with some lateral thinking if anyone can help

I have a number of products, 5 in total, and 2 category of customer : F and NF

Product NF F
1 Free Free
2 40 50
3 45 55
4 40 45
5 45 55

I have an order form which returns values of "yes" or blank for each product to a worksheet 'submissions'.

If customers order 4 or more, F pay 150 while NF pay 190 (saving 20 and 15 respectively)
If customers order 3, F pay 115 while NF pay 145
If 2 or less, individual prices apply.

I have a separate worksheet, Invoices, with a cell where i want to calculate a total based on submitted order.

I thought to use a multiple IF with COUNTA for the discounts and then deal with the individual price orders, but I think it has two many nested layers to work...is there another solution?

So far I got the discount deals done, but am not sure how to go about turning "yes" on submissions into the associated value for summing...hm.

=IF(AND([F or NF cell]="NF", COUNTA([Product cell range])>=4, 190,(IF(AND([F or NF cell]="F", COUNTA([Product cell range])>=4,150,
(IF, (AND([F or NF cell]="NF", COUNTA([Product cell range]=3, 145,
(IF(AND([F or NF cell]="F", COUNTA([Product cell range])=3, 115))))))

Thats as far as it goes. I need somehow to get 'If customer = NF and cell

#VALUE!