# 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