COUNTIFS function with multiple criteria
Hello!
I've been having trouble with getting this formula correct. Here are the details.
I want to count row A when "service expansion" is present only if row B has "qualifying" or "proposal" or "Negotiation" listed.
I've tried several variations but can't seem to get this one!
Best Answer
-
Hi,
You'll want to use COUNTIFS in conjunction with the OR function and the @cell variable. Here's an example:
=COUNTIFS(Tier:Tier, "Service Expansion", [IT Track]:[IT Track], OR(@cell = "Qualifying", @cell = "Proposal", @cell = "Negotiation"))
Make sure to change the column range references to fit the ranges that you need to reference in your sheet.
More on COUNTIFS: https://help.smartsheet.com/function/countifs
More on OR: https://help.smartsheet.com/function/or
More on @cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
Answers
-
Hi,
You'll want to use COUNTIFS in conjunction with the OR function and the @cell variable. Here's an example:
=COUNTIFS(Tier:Tier, "Service Expansion", [IT Track]:[IT Track], OR(@cell = "Qualifying", @cell = "Proposal", @cell = "Negotiation"))
Make sure to change the column range references to fit the ranges that you need to reference in your sheet.
More on COUNTIFS: https://help.smartsheet.com/function/countifs
More on OR: https://help.smartsheet.com/function/or
More on @cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell
-
I'm having difficulty with the multiple criteria. My current formula is: =COUNTIFS(Product Category:Product Category, "Clinic Subscription", [Sales Stage]:[Sales Stage], OR(@cell = "Proposal Submitted", @cell = "Short Listed", @cell = "Verbal Commitment", @cell = "Contract Negotiation", @cell = "Closed Won", @cell = "Closed Lost"))
I'm trying to all of the same product category in the various sales stages listed.
-
Good morning,
I am looking to use a CountIFS to do the following
If the # of Days since last follow is greater than 30 and the Issue Status is not equal to Completed and/or Duplicate I want to count the row.
I used the formula example provided in a previous chat and I am getting an #unparseable
Here is my current formula
=COUNTIFS([# of Days since last followup]:[# of Days since last followup], >30, [Issue Status]:[Issue Status], OR(@cell <>“Completed”, @cell <>"Duplicate”))
Please help me with where I went wrong - Thanks in advance
-
Nevermind got it to work with the following:
=COUNTIFS([# of Days since last followup]:[# of Days since last followup], >30, [Issue Status]:[Issue Status], <>"Completed", [Issue Status]:[Issue Status], <>"Duplicate")
-
Hi,
I am trying to use COUNTIFS with the CHILDREN function and cannot seem to get the formula correct.
I want to count those that belong to "Ann", but only those that have been "submitted" within the children of "Quarter 1". How would I write out this formula?
Here is what I have tried:
=CHILDREN([Short Title]2, COUNTIFS(GCA:GCA), "Ann"),([STATUS:STATUS], "Submitted")))
Thanks!
-
Hi,
Did you get the formula working or do you still need help?
Have a fantastic week!
Best,
Andrée StaråStarå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Can you please put an example on how can I use that formula but referencing another sheet in SmartSheet?
-
Never mind I was able to make it work
=COUNTIFS({Project Portfolio Summary April 2019 Range 1}, "In Progress", {Project Portfolio Summary April 2019 Range 2}, Lead@row)
-
I need help with the CountIfs formula where I reference the same sheet, but it only counts the item that meets both criteria.
-
What are your ranges and criteria?
=COUNTIFS(1st criteria range, 1st criteria, 2nd criteria range, 2nd criteria)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome ... I'm having this same issue - this is what I am trying to count from (2) columns, but I keep getting #UNPARSEABLE
=COUNTIFS(PS Qty:PS Qty, "2", Type:Type, "SM")
Thanks!
UPDATE:
Some how I was able to get this to work on one of my sheets (I have no idea how since I didn't do anything different). But when I try this exact same thing on another sheet with different Column names, it gives #UNPARSEABLE.
UPDATE #2
So, is it a thing that Smartsheet doesn't like numbers in Column names? The columns I was using on my other sheet contain "1x3". When I remove that from the column name, it worked.
-
@Nancy Heater When using column names with spaces, numbers, and/or special characters, the column names must be surrounded by square brackets.
=COUNTIFS([PS Qty]:[PS Qty], "2", Type:Type, "SM")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
HI,
I would like to make a countifs function work.
This is how I am doing it but it continues to give me an "Invalid operation error message. Please help!
*I want to count the number of times the title for a particular position is interviewed/screened during a specified date range.*
=COUNTIFS({Candidate Screening Tracker Range 1}, [Name of Position]@row, {Candidate Screening Tracker Range 4}, <=May1, >=June1)
-
Try this...
=COUNTIFS({Candidate Screening Tracker Range 1}, [Name of Position]@row, {Candidate Screening Tracker Range 4}, AND(@cell <= May1, @cell >= June1))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!