Date filter not in effect when I reference a Sheet. And, COUNTIFS/CONTAINS issue

Options

I am trying to pull info into a helper sheet from another sheet that I have filtered to only have items with a completed date in 2024.

When I then insert the reference to that filtered sheet it is not showing just the filtered items I want, but dates going back to 2020.

Is there a fix for that?

Once that is fixed, I would love to be able to count a how many times a few people have completed jobs.

For instance, we have 3 people that fall into one department and I want to group them all into 1 department on the helper sheet. Right now I can only get it to return info for 1 person.

=COUNTIF({Quote Person}, ("Mike Collison"))

That is my current formula, I have tried plugging "CONTAINS" and the other names that I want to include with no luck

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/15/24
    Options

    Create a reference for the completed date column as well then Use this formula

    =COUNTIFs({Quote Person}, "Mike Collison",Year({Date Ref}),2024)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Andre O
    Andre O ✭✭✭
    Options

    Hey, Mark, I am getting an Invalid Data Type error with the formula.

    =COUNTIFS({Quote Person}, "Mike Collison", YEAR({Quote Complete Year}), 2024)

    These are the two columns that I am referencing in the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!