COUNTIFS function with multiple criteria

RMaresca

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

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

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

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

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

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

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å

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

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

Jose Munoz

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

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

@Celeste Padron

What are your ranges and criteria?

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

Nancy Heater

@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

@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

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

@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

@Paul Newcome

Hey Paul! Thanks, it did not work :(

Any other suggestions?