Good Afternoon, I have a formula below and it returns 0 values.

Scott B 12

=COUNTIFS({Project Portfolio Tracker PM}, "Jane Smith", {Project Portfolio Tracker Range 8}, "In Progress", {Project Portfolio Tracker Range 8}, "Not Started" )

Can someone please advise me on what I am doing wrong?

Thanks,

Scott

Ray Lindstrom

@Scott B 12

Are both of your cross sheet references pointing to the same Status column? If so, you're still doing the same thing as you were originally which will still result in 0. You only need one cross sheet reference per range. It's my understanding that you are trying to count how many lines/tasks have Jane Smith as the PM and either In Progress or Not Started as the Status? If so, try this:

=COUNTIFS({Project Portfolio Tracker PM}, "Jane Smith", {Project Portfolio Tracker Range 8}, OR(@cell = "Not Started", @cell = "In Progress"))

I created two grids, used the same cross sheet reference names to test this, and it was successful.

Hope this helps!

BRgds,

-Ray

Ray Lindstrom

Hi @Scott B 12,

It looks like you're counting rows that meet multiple criteria.

The problem I see is that it's looking for {Project Portfolio Tracker Range 8} twice.

Once it needs a value of "In Progress", and once it needs a value of "Not Started".

That column likely only has one value or the other, and not both; so you formula will return 0.

If you're looking for Jane Smith who is in one or the other Status values, imbed an OR() as your second criteria for In Progress or Not Started (instead of having 3 criteria as 2 would be combined in the OR)..

Hope this helps!

BRgds,

-Ray

Scott B 12

Ray, Thank you for your quick response. I have tried naming the column with two separate names:

=COUNTIFS({Project Portfolio Tracker PM}, "Jane Doe", {Project Portfolio Tracker Range 6.5}, "In Progress", {Project Portfolio Tracker Range Status}, "Not Started") and I am still getting the same error. What I am trying to accomplish is how many projects does a PM have and the column has multiple values such as "In Progress' or "Not Started."

Is there a better way to accomplish this?

Thanks,

Scott

Ray Lindstrom

@Scott B 12

Are both of your cross sheet references pointing to the same Status column? If so, you're still doing the same thing as you were originally which will still result in 0. You only need one cross sheet reference per range. It's my understanding that you are trying to count how many lines/tasks have Jane Smith as the PM and either In Progress or Not Started as the Status? If so, try this:

=COUNTIFS({Project Portfolio Tracker PM}, "Jane Smith", {Project Portfolio Tracker Range 8}, OR(@cell = "Not Started", @cell = "In Progress"))

I created two grids, used the same cross sheet reference names to test this, and it was successful.

Hope this helps!

BRgds,

-Ray

Scott B 12

Ray, Thank you so much! That solved it. I really appreciate it.

Scott