Countifs with Multiple Criteria

rgallego18

Hello Smartsheet Community, 

I need another help from you. I am having a hard doing the countifs with multiple criteria.

I need to count the number of "In Progress" project per Project Lead but I it makes it complicated because there are some cells that have 2 names in it.

This is the formula I currently have in Smartsheet, to get the total number of projects that are in Progress under under Person A but it only counts the cell with a single name.

=COUNTIFS({RANGE 1}, "Person A", {RANGE 2}, "In Progress")

 

=COUNTIFS({RANGE 1}, "*Person A*", {RANGE 2}, "In Progress") 

-> this second formula, is an excel formula which counts all the entry for "Person A". I added asterisk (*) and it worked. This is exactly what I need but it everytime I do this I keep getting "0" value.

 

Thanks again for all the help

Andrée Starå

Hi,

Try this.

I've added a Helper Column called Names to put the name you're searching for, but you could change it in the formula instead if you like.

Change the ranges to the cross-sheet ranges you'd like to use.

=COUNTIFS(Status:Status; "In Progress"; [Project Lead]:[Project Lead]; FIND(Names@row; @cell) > 0)    

The same version but with the below changes for your and others convenience.    -=COUNTIFS(Status:Status, "In Progress", [Project Lead]:[Project Lead], FIND(Names@row, @cell) > 0)

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

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

rgallego18

Hi Andree, 

Thanks for the help! Tried it but it is not working or I'm doing it wrong.

Question.

When you say...

Status:Status - is this the 1st RANGE?

[Project Lead]:[Project Lead] - the 2nd RANGE?

I'm also lost on this part: FIND(Names@row, @cell) > 0)

Is the Names@row another range like RANGE 3?

I entered it this way and I keep getting an this message -> #UNPARSEABLE

 

=COUNTIFS({Range 1}, "In Progress", {Range 2}, FIND(Person A), {Range 3}>0))

Andrée Starå

Happy to help!

I've added my answers below.

Question.

When you say...

Status:Status - is this the 1st RANGE?

I've changed the order, so this is the Range for the "In Progress"

[Project Lead]:[Project Lead] - the 2nd RANGE?

I've changed the order, so this is the Range for "Person A, Person B" and so on.

I'm also lost on this part: FIND(

Names@row, @cell) > 0)

Is the Names@row another range like RANGE 3?

This is a list of the names in a list on the same sheet, but you could change it in the formula instead.

I entered it this way and I keep getting an this message -> #UNPARSEABLE

=COUNTIFS({Range 1}, "In Progress", {Range 2}, FIND(Person A), {Range 3}>0))

Try this.

=COUNTIFS({Range for status}, "In Progress", {Range for Project Lead}, FIND("Person A", @cell) > 0)

Did it work?

Best,

Andrée

 

rgallego18

Hey Andree!

You're such a Smartsheet Wizard!!!

It worked, yay!!!

THANK YOU SO MUCH!!!

Andrée Starå

I'm always happy to help!

Smartsheet Wizard! That's a new one! Thanks! 

Happy to hear that you got it working!

Best,

Andrée

Jeff Muschett

Hi @Andrée Starå , I'm having a similar issue. Your formula above, =COUNTIFS({Range for status}, "In Progress", {Range for Project Lead}, FIND("Person A", @cell) > 0) worked for me, however there is one snag.

In my second range (Range for Project Lead in the above formula) I have two columns selected. Let's call them Project Lead and Project Manager. When I add both columns to that tracker range, I get an #INCORRECT ARGUMENT flag.

The request I have is "I want to know how many in progress projects person A is either a Project Lead or Manager on."

Thanks!

Andrée Starå

Hi @Jeff Muschett,

What’s your formula?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD