Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

COUNTIF formula across Multiple Columns

Rocky Arnold
edited 12/09/19 in Archived 2016 Posts

I am trying to count the number of times an individual's name is input across multiple columns.


For example, an individual may be an "Account Manager" on one project but a "Project Manager" on another. I want to count the total number of projects this individual is involved with, regardles the role. Below is the formula I've developed, but I get the "#INVALID DATA TYPE" error.


=COUNTIFS([Current Phase]:[Current Phase], [P's in MP]3, OR([Account Manager]:[Account Manager], [Design Manager]:[Design Manager]), [Calc Value]11)





  • Greg Gates
    Greg Gates ✭✭✭✭✭

    If your columns are adjacent to each other, you can actually specify more than one column in the range for COUNTIF. You might be able to do something like:


    =COUNTIF([Account Manager]:[Design Manager], "Rocky")


    to count how many times "Rocky" appears in all of the cells between the Account Manager and Design Manager columns.


    If your columns aren't adjacent to each other, you would have to break this out into separate statements and add them together:


    =COUNTIF([Account Manager]:[Account Manager], "Rocky") + COUNTIF([Project Manager]:[Project Manager], "Rocky") + ...


    I hope that helps! Let me know if this doesn't work for you or if I misunderstood your scenario.

  • Rocky Arnold

    Greg, thank you for your thoughtful and meticulous response!


    The columns are adjacent, but for some reason it returned an "Invalid" response when I tried your first method.


    However, I added the "COUNTIFS' and it worked fantastic.


    Thank you so much for your help!!!

This discussion has been closed.