Formula Question

Options
✭✭✭
edited 12/09/19

Good Morning.

I am attempting to create a forumula which references another sheet; however, I continue to receive error messages or it doesn't calculate correctly.  Below is the outline of what I am looking to accomplish.  Let me know if you can help.

One column called Program with drop down options of Service Expansion, Frontline Fast, Readyline Express.

Another column called Status with drop down options of Qualifying, Initial Meeting/Needs Analysis, Proposal, Negotiation, Closed Won, Not Interested, Doesn’t

I am trying to create a formula, in a seperate worksheet, that will count all the ‘Service Expansions’ in the program column WHEN the Status is “Qualifying, Initial Meeting/Needs Analysis, Proposal, or Negotiation”.

I have attached a screen shot of the formula I have currently.  I have also tried a countif / if statement with no success.

Appreciate any assistance.

Thank You.

~Christine~

Tags:

• ✭✭✭✭✭✭
Options

Your countifs statement is trying to determine if ALL of those criteria are true. The way to accomplish this by creating individual countif statements and combining them with simple math =Countif([Range Name]:[Range Name], "Criterion")+Countif([Range Name]:[Range Name], "Criterion") + countif([Range Name]:[Range Name], "Criterion")...

Or you can also use an OR statement like in the example shown here: You have to use @cell = to set what you're looking for in the cell.

=COUNTIF([Range]:[Range], OR(@cell = "CRITERION", @cell = "CRITERION"))

• ✭✭✭
Options

Thank You Mike.  I believe I had tried that yesterday as well - but will try again

Thank You.

• ✭✭✭
Options

Mike, these recommendations seem to count if they are all of those criteria; but not if column X = xyz AND column Y = a or b or c

Any other recommendations?

• ✭✭✭
edited 03/08/18
Options

Mike, can you explain the @cell functionality?

When I type that in I can not access another sheet to notate the cells?

I also attempted a 'count if' AND 'count if' and received #unparseable ???

• ✭✭✭✭✭✭
Options

Try this...

=COUNTIFS(range1, criterion1, range2, criterion2a) + Countifs(range1, criterion1, range2, criterion2b) + Countifs(range1, criterion1, range2, criterion2b)

Should do the trick for you, though it is a fairly long concatentaion of formulas.

• ✭✭✭
Options

THANK YOU THANK YOU THANK YOU THANK YOU!

That did it.  Truly appreciate your willingness to help me on this.

Have a great day.  Thanks Mike!!!

• ✭✭✭✭✭✭
Options

Yay. Glad I could be of help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!