Sheet Summary Formula versus Sheet/Report Filtering

Nick Stamatakis

Hi all,

I've been scratching my head on this for a while and I can't resolve why the filtering I do on a sheet/report is not matching my formula on a sheet (upon which the report is based on). Here it goes:

I have an intake sheet for my projects that when a PM creates a project via Control Centre, it spits out the expected output in a portfolio summary sheet. From that portfolio summary sheet, I created a report to list all my active projects (In Progress, Not Started) based on a specific service line. From there, I can get a value ("X").

On that same sheet (portfolio summary), I can create a simple filter on the Project Status and Service Line and match the count; works great as a validation tool. Now, when I create a sheet summary field (for later use as a widget in a dashboard) on that portfolio summary sheet, based on the same criteria, my counts don't match. This is the formula I've used:

=COUNTIFS([Project Status]:[Project Status], OR(@cell = "In Progress", @cell = "Not Started"), [Service Line]:[Service Line], "ABC")

In that Service Line column, I have 4 distinct options a user can select. It is a multi-select (not single-select) drop down for reference.

I've even morphed my formula to be:

=COUNTIFS([Service Line]:[Service Line], CONTAINS("ABC", [Service Line]:[Service Line]))

In fact, when I use either of these formulas, I just get a value of zero (when I certainly expect a larger number).

I take my counts from the portfolio summary sheet and my reports as an accurate count and can't figure out why the formulas don't work. Any advice? Thanks in advance!

Kelly Moore

Hey @Nick Stamatakis

The syntax of the CONTAINS, when part of a COUNTIFS, is not what smartsheet is expecting. Try this:

=COUNTIFS([Project Status]:[Project Status], OR(@cell = "In Progress", @cell = "Not Started"), [Service Line]:[Service Line],CONTAINS("ABC", @cell))

*Assuming ABC is a placeholder, don't forget to change it to the real thing

If you're still getting an unexpected results, when I am troubleshooting a formula, I begin dissecting the formula to try to understand what piece is presenting problems.

Try this for troubleshooting purposes only, try this:

COUNTIFS([Service Line]:[Service Line],CONTAINS("ABC", @cell)) do you still get a zero?

if still zero, what about with this?

=COUNTIFS([Service Line]:[Service Line], HAS(@cell, "ABC")

If all of these work, check the spelling and case of In Progress and Not Started vs your dropdown.

Did anything work for you?

Kelly

Kelly Moore

Hey @Nick Stamatakis

The syntax of the CONTAINS, when part of a COUNTIFS, is not what smartsheet is expecting. Try this:

=COUNTIFS([Project Status]:[Project Status], OR(@cell = "In Progress", @cell = "Not Started"), [Service Line]:[Service Line],CONTAINS("ABC", @cell))

*Assuming ABC is a placeholder, don't forget to change it to the real thing

If you're still getting an unexpected results, when I am troubleshooting a formula, I begin dissecting the formula to try to understand what piece is presenting problems.

Try this for troubleshooting purposes only, try this:

COUNTIFS([Service Line]:[Service Line],CONTAINS("ABC", @cell)) do you still get a zero?

if still zero, what about with this?

=COUNTIFS([Service Line]:[Service Line], HAS(@cell, "ABC")

If all of these work, check the spelling and case of In Progress and Not Started vs your dropdown.

Did anything work for you?

Kelly

Nick Stamatakis

Hi @Kelly Moore,

That certainly did the trick. I used your first formula suggestion and it worked. "ABC" was a placeholder so I updated it to what I need accordingly. I appreciate you and your support!

Until next time...