Google-sheets – “IF” Functionality Inquiry

formulasgoogle sheets

So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount.

So as if a + b = 1 then equals $35

=COUNTIFS(F3:F100,"Y",B3:B100, "INCOMING")

I did it a simple way and just had whatever number it comes up with multiplied it by 35 in another column. This works for me, but if there is another formula that would be awesome to know

Then how would I figure out how to do a similar formula, but add everything BUT "INCOMING" in the B column.

So if a + b (which excludes "INCOMING") = 1 then equals $25

Pretty much I need it to just populate the price ($35) every time "INCOMING" pops up in the "B" column and if column "F" has a "Y" in it.

enter image description here

Also when a certain number is reached it will add a number.

x = 15, 20, 25, 30, 35, 40 then add $100

Whatever formula this will be is there a way to have it add another $100 whenever it hits the next increment of 5, but only up until 40?

Thank you in advance!

Best Answer

Try this for your second formula:

=IF(OR(COUNTIFS(F3:F,"Y",B3:B, "INCOMING")>=15,COUNTIFS(F3:F,"Y",B3:B, "INCOMING")<=45),if(mod(COUNTIFS(F3:F,"Y",B3:B, "INCOMING"),5)=0,"add extra amount","don't add extra amount"),"don't add extra amount")

  • COUNTIFS(F3:F,"Y",B3:B, "INCOMING"): Your basic COUNTIFS formula
  • OR(COUNTIFS>=15,COUNTIFS<=40): this only permits COUNTIFS values between 15 and 40
  • mod(COUNTIFS(F3:F,"Y",B3:B, "INCOMING"),5): a MOD statement on COUNTIFS, using 5 as the divisor
  • if(mod(COUNTIFS(F3:F,"Y",B3:B, "INCOMING"),5)=0: If the MOD result = 0, then the dividend must be 15,20,25,30,35 or 40; if the result <>0, the the number is not one of those values.
  • the combined formula tests: If COUNITS >=15 OR <=40, THEN if the mod result = 0, pay the extra amount, otherwise don't pay the extra amount.
Related Topic