Editing an existing formula to add multiple IF formulas and other requirements

I've been using a formula for accounting to calculate sales within a bunch of states that looks like this:

=IF(COUNTIF(G2150,"MA")+COUNTIF(G2150,"CT")+COUNTIF(G2150,"RI"),IF(EXACT(L2150,"Apple"),(N2150*2),(N2150*4)),"")

This formula currently works, however I would like to add on to it and haven't been able to figure it out.

It currently works out as:

• If the state column G is MA, RI, or CT, and if the product column L is Apple then you multiple the quantity column by 2.
• If it's not Apple, you multiple it by 4.
• If it's not in those states you leave it blank.

This formula works great if there are only 2 products involved.

We are adding in new inventory that has different pricing. I need to alter it to something that works like this:

• If the state column G is X, Y, or Z and if the product column L is Apple, then multiple the quantity column N by 4.
• If the product in those same states is Orange in column L, then multiply by 2.
• If the product from a different column K in those same states is Pineapple then multiply the quantity amount N by 3.
• Anything not in those states, leave blank. And so on.

Is this possible?

I have three products / three prices from column K to add, plus two products from column L. All of the quantities are in column N.

Use OR with an array for the states:

OR(G2150={"MA","CT","RI"})

Then use some Booleans to return the correct multiplyer.

((L2150="Apple")*4)+((L2150="Orange")*2)+((K2150="Pineapple")*3)

So:

=IF(OR(G2150={"MA","CT","RI"}),(((L2150="Apple")*4)+((L2150="Orange")*2)+((K2150="Pineapple")*3))*N2150,"") • I tried this but for some reason the cells that would have the data in them are coming out to 0 – user921816 Jul 10 '18 at 15:00
• Not sure why, as you can see in the photo it is working for me. – Scott Craner Jul 10 '18 at 15:02
• It is happening when I try to do a specific type of pineapple. I tried adding asteriks but it won't budge. Do you know how to make it so that if it contains the word pineapple it will count? image.ibb.co/b4ehCT/Screen_Shot_2018_07_10_at_11_46_41_AM.png – user921816 Jul 10 '18 at 15:56
• @user921816 that was not part of the requirements. But replace (K2150="Pineapple") with (ISNUMBER(SEARCH("Pineapple",K2150))) – Scott Craner Jul 10 '18 at 15:58
• I didn't realize it would require an alternate formula. I've added asteriks in the past and it worked. Thank you for the help! – user921816 Jul 10 '18 at 16:05