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!


2 Answers 2


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.

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

There are several ways that this can be done. Here are two


=if(COUNTIFS(F3,"Y",B3, "INCOMING")=0,0,35)

  • COUNTIFS(F3,"Y",B3, "INCOMING"): COUNTIFS evaluates Column B for "INCOMING" and Column F for "Y". It will return a value of 0 unless both criteria are satisfied.

  • IF(COUNTIFS=0,0.35): the IF statement tests the value from the COUNTIFS. if the value = 0, then it returns 0, otherwise it returns 35.

2 - IF using AND


  • AND(B3="INCOMING",F3="Y"): this combines two criteria and requires a "true" result from both
  • IF(AND,35,0): the IF statement evaluates the AND statement, if the value is true, then the result is 35, otherwise the value is 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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