0

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!

  • Welcome. "So I am trying to figure out how to whenever it's a "Y" and "INCOMING", then it will equal a dollar amount." Just what number would you like it to be?; Your Countifsseems to be working fine - it returns a value of one for the combination of :"Y" AND "INCOMING" - which is the correct result. – Tedinoz Mar 4 at 13:45
  • "Also when a certain number is reached it will add a number." So, exclude a result = "5" or "10"? – Tedinoz Mar 4 at 13:47
  • Please remember that as per site guidelines when an answer addresses your question, accept it so others can benefit as well. – marikamitsos Mar 5 at 8:13
0

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.
| improve this answer | |
  • So I made a mistake when posting this. I made it seem like I wanted it all for only "INCOMING" when I didn't. 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. Sorry about the confusion. That's for just one of the questions I asked. – Daniel Mathews Mar 4 at 23:26
  • @DanielMathews Thanks for pointing this out. Would you please edit your question so that it describes EXACTLY what you want and users can read and understand it. "That's for just one of the questions I asked." There a basic rule on StackOverflow - one question per post, though sometimes a question on a related issue is accepted. Please decide what question you want to address; if you have more questions, then it might be best to raise separate posts for them. – Tedinoz Mar 4 at 23:33
  • Most definitely...Didn't know that. Going to change that now! Thank you for the help and letting me know this! :) – Daniel Mathews Mar 6 at 18:34
0

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

1 - COUNTIFS

=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

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

  • 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.
| improve this answer | |

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.