Formula not returning correct data

09/15/20
Answered - Pending Review

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)

Answers

  • 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?

  • 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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    What column type is {Completed Project Owner}?

  • 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 NewcomePaul 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.

Sign In or Register to comment.