# COUNTIFS function with multiple criteria

Options
edited 06/30/22

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!

Tags:

• Employee
Options

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

• Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

Can you please put an example on how can I use that formula but referencing another sheet in SmartSheet?

• ✭✭✭✭
Options

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)

• Options

I need help with the CountIfs formula where I reference the same sheet, but it only counts the item that meets both criteria.

• ✭✭✭✭✭✭
Options

What are your ranges and criteria?

=COUNTIFS(1st criteria range, 1st criteria, 2nd criteria range, 2nd criteria)

• ✭✭✭✭
edited 11/25/20
Options

@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.

• ✭✭✭✭✭✭
Options

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

• Options

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)

• ✭✭✭✭✭✭
Options

Try this...

=COUNTIFS({Candidate Screening Tracker Range 1}, [Name of Position]@row, {Candidate Screening Tracker Range 4}, AND(@cell <= May1, @cell >= June1))

• Options

Hey Paul! Thanks, it did not work :(

Any other suggestions?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!