How do you capture accurate COUNTS by excluding certain criteria?

Beverly Troxtell

Here's my use case:

I have an Actives Projects smartsheet that captures all data associated with our projects in flight, including Project State and Requesting Team. I created a separate smartsheet so that I capture specific data that I can then use on an exec summary dashboard.

In the first column, I have a list of Requesting Teams (e.g., HR Ops, Talent Dev, etc.). The second column will be a Count column that reflects # of projects that are In Progress (pulled from Active Project smarsheet's Project State column) for each Requesting Team. I have been able to do that with this formula, e.g.:

=COUNTIFS({Project Log-ACTIVE PROJECTS Range 1}, "HR Ops")

Here's the challenge...I want to EXCLUDE the "On Hold" statuses from the Project State column so that the count accurately reflects in progress projects.

Hope that makes sense. I've searched several community posts, but nothing I came across worked for my use case.

Thank you!

Beverly

Paul Newcome

This SHOULD work for looking at everything that is not blank and not "On Hold":

=COUNTIFS({Project Log-ACTIVE PROJECTS Requesting Team}, "HR Ops", {Project Log-ACTIVE PROJECTS Project State}, AND(NOT(ISBLANK(@cell)), NOT(CONTAINS("On Hold", @cell))))

To add "Not Started" to your most recent solution:

=COUNTIFS({Project Log-ACTIVE PROJECTS Range 1}, "HR Ops", {Project Log-ACTIVE PROJECTS Range 4}, OR(@cell = "In Progress", @cell = "Not Started"))

L_123

You can try:

=COUNTIFS([Project State]:[Project State], not(@cell = "On Hold")

This will count blanks as well, so you may want to alter it to get rid of blanks. See below

=COUNTIFS([Project State]:[Project State], not(and(@cell = "On Hold",isblank(@cell

Beverly Troxtell

Hi. I get #UNPARSEABLE. What I see missing is the reference to the other smartsheet that this smartsheet is pulling data from and also the reference to the Requesting Team. Any other ideas? Here was my original formula, which was working... but was pulling in both In Progress and On Hold statuses for the HR Ops team (instead of excluding On Hold)

=COUNTIFS({Project Log-ACTIVE PROJECTS Range 1}, "HR Ops")

Paul Newcome

Try something like this...

=COUNTIFS({Project Log-ACTIVE PROJECTS Requesting Team}, "HR Ops", {Project Log-ACTIVE PROJECTS Project State}, AND(NOT(ISBLANK(@cell)), NOT(CONTAINS("On Hold", @cell))))

Beverly Troxtell

Darn, now that just gave me #INVALID REF. Here are some screenshots.

Smartsheet 1 is where the formula goes into (note that in this example, I have the requesting team as Talent: Global Talent Development...was easier to screen cap as it was at the top).

Smartsheet 2 (Project Log-ACTIVE PROJECTS) is where the data is being referenced.

Paul Newcome

Are you following the proper steps to create cross sheet references? The invalid reference error means that one or more cross sheet references in your formula are incorrect.

When you start typing a formula, a small dialog box should pop up below the cell. When you get to the point where you designate a range, click on the link in that box that says to reference another sheet. Another window should come up where you can select the sheet to reference. Then you select your range (clicking on a column header will reference the entire column). Then click the smaller blue button that says "Insert Reference".

By default, the range name will be {Sheet Name Range #} where the number is going to go in order depending on how many references you have created to that sheet and left numbered.

I personally find it much easier in the long run if I leave the sheet name, but then include the range name such as {Sheet Name Column Name} if I am referencing an entire column.

Beverly Troxtell

Yes, the cross sheet references are in there, and the formula worked previously when I wasn't trying to exclude "On Hold" statuses. I also tried it with the Range #.

This was the closest I could get to a working function, although still not perfect as it doesn't include "Not Started" project state:

=COUNTIFS({Project Log-ACTIVE PROJECTS Range 1}, "HR Ops", {Project Log-ACTIVE PROJECTS Range 4}, "In Progress")

Thanks to all who contributed ideas. This got me a little closer at least.

Paul Newcome

I just ran into a similar problem working in one of my sheets. I wonder if there might not be a bug floating around.

I ended up having to go to the source sheet and removing all cross sheet references to my target sheet and then rebuilding all of my formulas from scratch. Fortunately this particular sheet was a pretty straight forward build, but it is still frustrating.

If you have a complicated sheet, you may want to contact support to see if they can refresh both sheets on the back end.

As long as the references are created correctly (which you said they are) there is no reason your formula should not be working.

Beverly Troxtell

Thanks Paul! We must have typed at the same time, as I submitted an update above to my last post. It was strange that the last several days nothing was working. I did the same where I had to rebuild the sheet this morning (As a last ditch effort), and then this combo worked. Still, would be good to be able to figure out the EXCLUDE On Hold formula for complete accuracy. Thanks again!

Paul Newcome

This SHOULD work for looking at everything that is not blank and not "On Hold":

=COUNTIFS({Project Log-ACTIVE PROJECTS Requesting Team}, "HR Ops", {Project Log-ACTIVE PROJECTS Project State}, AND(NOT(ISBLANK(@cell)), NOT(CONTAINS("On Hold", @cell))))

To add "Not Started" to your most recent solution:

=COUNTIFS({Project Log-ACTIVE PROJECTS Range 1}, "HR Ops", {Project Log-ACTIVE PROJECTS Range 4}, OR(@cell = "In Progress", @cell = "Not Started"))

Beverly Troxtell

Thank you, Paul! That last one did the trick...it's a xmas miracle! :) Happy Holidays!

Paul Newcome

Excellent. Happy to help! 👍️🎄