Hey! I am looking for a formula that will count projects by user but exclude

Right now i have a list of projects with the users who are doing them, and each project has a status. I want to total each project by user name but exclude any projects with Closed Won or Cancelled status. This is what I have below which is giving me the total projects but it is not excluding the Closed Won status..? help!

=COUNTIFS({RFP Tracker Range 3}, Metric@row, {RFP Tracker Range 3}, OR(@cell <> "Closed Won"))

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    edited 12/07/22 Answer ✓

    @spothier1 You were close. It looks like your formula is referencing the same RFP Tracker Range 3. Was that a typo?

    I'm guessing your Metric column lists your user names and RFP Tracker Range 3 is also the list of names.

    Try this one...

    =COUNTIFS({RFP Tracker Range 3}, Metric@row, {RFP Tracker Range X}, AND(@cell <> "Closed Won", @cell <> "Cancelled"))

    Be sure to update the {RFP Tracker Range X} above with the correct reference name for the status column.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!