0

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.

0

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,"")

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.