COUNTIFS function with multiple criteria

RMaresca · 03/07/18

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!

Shaine Greenwood · 03/09/18

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

Shaine Greenwood · 03/09/18

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

Randy Cook · 07/26/18

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.

bday2329 · 12/03/18

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

bday2329 · 12/04/18

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

wroger · 02/08/19

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!

Andrée Starå · 04/03/19

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

Jose Munoz · 04/10/19

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

Jose Munoz · 04/10/19

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)

Celeste Padron · 07/21/20

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

Paul Newcome · 07/21/20

@Celeste Padron

What are your ranges and criteria?

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

Nancy Heater · 11/25/20

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

Paul Newcome · 11/30/20

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

Jessica Toledo · 06/21/21

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)

Paul Newcome · 06/21/21

@Jessica Toledo

Try this...

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

Jessica Toledo · 06/21/21

@Paul Newcome

Hey Paul! Thanks, it did not work :(

Any other suggestions?

COUNTIFS function with multiple criteria

Best Answer

Answers

Help Article Resources

Categories