Formula Question

clifka

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~

 

Mike Wilday

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

clifka

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

Thank You.

clifka

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?

clifka

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 ???

Mike Wilday

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.

clifka

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!!!

Mike Wilday

Yay. Glad I could be of help!