Combining multiple criteria in COUNTIFS formula

caw98776

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

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.