Combining multiple criteria in COUNTIFS formula

caw98776 · 08/05/19

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

Mike Wilday · 08/05/19

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 · 08/05/19

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å · 08/05/19

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 · 08/05/19

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 · 08/05/19

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 · 08/05/19

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 · 08/05/19

Genius! It works!

Andrée Starå · 08/05/19

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 · 08/07/19

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.

Combining multiple criteria in COUNTIFS formula

Comments

Help Article Resources

Categories