Combining multiple criteria in COUNTIFS formula

There seems to be an error in my formula. The first part works by itself (counting the number of times the SME's name appears). Then, when I add the critieria to limit the count to those whose status is "Red", I get an "incorrect argument" error. It is selecting the correct data sets to pull from. Please look at my formula and tell me if you can identify a missing comma, parenthesis, or operator. The formula is:

=COUNTIFS(SMEA:SMEC, = "Jane Doe", Status:Status, = "Red")

Find more posts tagged with

Formulas

Comments

Mike Wilday

I wonder if its because your first range has multiple columns and your second range doesn't match. Ttry setting it to =COUNTIFS(SMEA:SMEA, ="Jane Doe", Status:Status, = "Red")

Does it work then?

caw98776

It does BUT I only receive 1/3 the hits I should because the name could appear in multiple columns. Is there a way to make it repeat the count for each column and then add them together?

Andrée Starå

Hi Christie,

You'd have to use the FIND function for it to work.

I'll get back to the post later if you've not solved it before.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

caw98776

I'd appreciate that. I thought perhaps I could repeat the formula three times and then have it add up but, since I've been using smartsheets a total of one day, I can't figure out how to do nested formulas and no examples seem to be available. There's a seminar but I need something I can refer back to.

Thanks!

Nic Larsen

Andree or Mike will likely have a better formula, but to answer you other question, you can stack them like this:

=COUNTIFS(SMEA:SMEA, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEB:SMEB, "Jane Doe", Status:Status, "Red") + COUNTIFS(SMEC:SMEC, "Jane Doe", Status:Status, "Red")

Mike Wilday

Good job Nick. I was overcomplicating the matter trying to use @cell references embedded in an OR statement. Haha. That would be the right formula to use.

caw98776

Genius! It works!

Andrée Starå

Try something like this.

=COUNTIFS(SMEA:SMEA; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEB:SMEB; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red") + COUNTIFS(SMEC:SMEC; FIND("Jane Doe"; @cell) > 0; Status:Status; "Red")

The same version but with the below changes for your and others convenience.

=COUNTIFS(SMEA:SMEA, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEB:SMEB, FIND("Jane Doe", @cell) > 0, Status:Status, "Red") + COUNTIFS(SMEC:SMEC, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Paul Newcome

Another option would be to create an additional column (SMEall for example) where you join the SME columns and then reference that in a single COUNTIFS.

SMEall would hold a basic

=JOIN(SMEA@row:SMEC@row, ",")

Then use

=COUNTIFS(SMEall:SMEall, FIND("Jane Doe", @cell) > 0, Status:Status, "Red")

Just another option if you run into a similar problem in the future but have more than just a few columns to add together.

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.