COUNTIF & COLLECT Functions Not Working


Data Example:

Formula Using:

=COUNTM(COLLECT({Project ID}, {Project Manager(s)}, $Label@row))

Where row = Project Manager Name

As you can see in the actual results, it's not counting the project ID at all when there is project manager.



  • ericncarr
    ericncarr ✭✭✭✭✭

    Hi @jazzmin.yuson - It looks like your formula is only looking for cells that are equal to "Jane Doe" or "John Doe" so the counts you are getting are correct in that sense.

    To get what the results you are expecting, I would use CONTAINS, and I think Countif might make the whole thing simpler:

    =COUNTIF({Project Manager(s)}, CONTAINS($Label@row, @cell))

  • jazzmin.yuson

    Hi @ericncarr Thank you so much that worked! If I add another column like release for example and want the numbers categorized by release how would the formula change then?

    This is what I thought it would be: =COUNT(COLLECT({Project ID}, {Release}, Orange, {Project Manager(s)}, $Label@row))

    See example Below -

  • ericncarr
    ericncarr ✭✭✭✭✭

    @jazzmin.yuson glad that worked!

    You can modify it to be a COUNTIFS and just add another criteria range and criteria (Release being the range and the criteria being either "Pear" or "Orange"

    =COUNTIFS({Project Manager(s)}, CONTAINS($Label@row, @cell), {Release}, "Pear") 

    =COUNTIFS({Project Manager(s)}, CONTAINS($Label@row, @cell), {Release}, "Orange") 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!