Formula not returning correct data

Mary Podvin

Hello,

I'm trying to count the number of projects by project owner and when it was completed. Below is my formula. It is returning data, but not the correct data. I'm sourcing from another Smartsheet. How can this be adjusted to work properly?

=COUNTIFS({Completed Project Owner}, [Project Owner 2]1, {Completed Projects Week #}, >35)

David Tutwiler

The first thing I would suggest is making your Project Owner field an absolute value by adding a $ (ie. [Project Owner 2]$1). Without it, your formula may be moving down the line and selecting the incorrect project owner in your count.

If that doesn't work, would you mind providing the expected result vs what you're actually getting?

Mary Podvin

Thanks, the absolute value isn't the issue- I want my formula to move down the line and adjust project owner.

For example, I'm getting a result of 14 for one owner, when if I use filters on the selected sheet she has 18 projects that should meet the countifs formula. Every other owner has projects coming back at 0, when they have projects that were delivered after week 35.

David Tutwiler

Logically it should work. Would you be able to provide screen shots of the sheets and references? I'm afraid without those I would just be guessing.

But another guess would be that maybe the Project Owner in the first example (only getting 14 when you should be getting 18) is spelled or formatted slightly differently? If it's free form text then David Tutwiler and David.Tutwiler would be seen as two different values and wouldn't match the conditions in your statement. It's a long shot, but I'm just trying to work through things that could cause issues in this scenario.

Paul Newcome

What column type is {Completed Project Owner}?

Mary Podvin

Hey- Thanks all for the responses. For some reason after waiting for a while (I had a few meetings) it seemed to work. Is there a proper sequence for saving/refreshing that I should be aware of?

Paul Newcome

Depending on the overall size and complexity of your sheet(s), it could just take a little bit of time for everything to catch up.