Formula Question

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~

formula assistance.png

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…