Countif formula with dynamic range

I'm trying to get a countifs formula to work where I specify a dynamic range. I have a column in my sheet with each row populated the name of one of a handful of assignees - I would like to specify a countifs range to be any row where the assignee column matches a specific name. I would then set the formula to count every cell within that range that matches a specified date.

My struggle is with the first part, I can't figure out how to define my range based on which rows that have a particular name in the assignee column. Is there any way to do that?


  • Jon Mark H
    Jon Mark H ✭✭✭✭✭

    Is this what you're looking for?

    =COUNTIFS([Assignee Name]:[Assignee Name], "Bob")

    Obviously substitute "[Assignee Name]" with the name of the column of the assignee

    And substitute "Bob" with the name of the assignee.

    Then add in the date part of the formula as you were saying.

    Let me know if you need any other input on this!

  • aporter
    aporter ✭✭

    Thanks for the reply! In your example, I want the Countifs formula to only search for the rows where "Bob" is the assignee, then count the number of cells that match the date I'm looking for within those rows. Effectively limiting the counting range with some sort of horizontal lookup of the rows that match my Bob criteria.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!