Want to count number of instances in a column

HI There,

I have one column in which I track the status of each row with a traffic light (Red, Yellow, Green). I would like to count the number of Red's, Yellow's and Green's in the column so I can use this information in a dashboard/portal to show the number of each color/status.

I have triied a few things, but I am farily unfamiliar with formula's. I must be doing something wrong as I get an 'invalid operation' message.

=COUNTIFS({Tracker Range Status}, FIND("Green", @cell),

What is the best way forward?


Best Answer


  • handmadetsunami
    handmadetsunami ✭✭✭✭

    You can use the following to count all the Greens in a column

    =COUNTIF([Column Name]:[Column Name], "Green")

    Hope this helps

  • Thank you for this explanation. The only practical matter is if I do the counts for mutiple statusses I would create a new grid.like this. Is the 'reference to another sheet' correct?

    =COUNTIF({Reference to anothersheet Status Column} [Column Name]:[Column Name], "Green")

    Many thanks for your assistance.


  • I think I have figured it out.Thank you for your help

  • handmadetsunami
    handmadetsunami ✭✭✭✭

    I believe the below will work for you and just select the column you want to count

    =COUNTIF({Reference to Another Sheet}, "Green")

  • HI Handmadetsunami, How would I go about counting over three columns:

    Workstreams: Workstream 1, Workstream 2, Workstream 3, etc

    Category: Action, issue, Risk

    Status: Open, Closed

    So Now I would like to count per workstream1, all Actions that have Status open. Then for workstream1, all issues that have status open. etc.

    Looking forward to your reply.


  • handmadetsunami
    handmadetsunami ✭✭✭✭

    You'll have to create one for each of your workstreams but this should be what you are looking for

    =COUNTIFS([Workstream Column]:[Workstream Column], "Workstream 1", [Category]:[Category], "Action", [Status]:[Status], "Open")

  • Ha Handmade tusnami,

    Directly in the sheet it works for me,. When I want to create a seperate summary sheet I run into parsing issues. What is going wrong here:

    =COUNTIFS({RAIDQ log Workstream}, Workstream:Workstream, "HCM", Category:Category, "Action", Status:Status, "Open")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots so that we can see the setup and column names of both sheets? Sensitive/confidential information can be removed, blocked, or replaced with "dummy data".

  • from a sheet like the below I aam creating an seperate grid/summary sheet;

    The summary I want to create is:

    for stream 1, category = Action, no of Open items = 2

    for stream 1, category = Risk, no of open items = 1


    In this way I can create a dashboard with per stream the no of open items per catergory.

    Hope this helps.


  • I see what I was doing wrong. I was also taking into account the column names, Column:Column, which isn't required.

    many thanks, I got it working now.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    Please don't forget to mark an "Accepted Answer" so that others know a solution has been found.

  • Ezra
    Ezra ✭✭✭

    If you have a column with number-based items, this might help:

    I have a column that can be populated with any three digit number. Most of the entries are just looking at the parent row with =PARENT()

    These are all work ticket IDs inside of a project to designate different phases. Most of them start out in the 001 to 035 range, which renders in Smartsheet as '001 (with the apostrophe only showing if you edit the cell). In a helper column that gets hidden while working on the gantt, I grab the raw number out of each row with =VALUE(WorkTicket@row)

    In the project data sheet, I then have twenty rows dedicated to telling me what distinct work tickets are used and how many times. The {WTRange} is looking at the helper column in the gantt.

    [Data]100 :: "Work Ticket Listing:"

    [Description]101 :: "Count of WTs"

    [Data]101 :: =IFERROR(LARGE({WTRange}, SUM(Description$101:Description101)+1), "")

    [Description]101 :: =IF(ISBLANK([Data]@row), "", COUNTIF({WTRange}, [Data]@row))

    [Data]102 :: =IFERROR(LARGE({WTRange}, SUM(Description$101:Description102)+1), "")

    [Description]102 :: =IF(ISBLANK([Data]@row), "", COUNTIF({WTRange}, [Data]@row))

    [Data]103 :: =IFERROR(LARGE({WTRange}, SUM(Description$101:Description103)+1), "")

    [Description]103 :: =IF(ISBLANK([Data]@row), "", COUNTIF({WTRange}, [Data]@row))

    etc... for the next rows

    --- side note: I did try using =DISTINCT and a few other ways to capture the worktickets, but found this to be the best, most rigorous method. In the case of "Stream 1" through "Stream 1847" you could have the helper column perform a =SUBSTITUTE([Workstream]@row,"Stream","")

  • Kimbh
    Kimbh ✭✭✭

    Hi, how can you do this when counting from another sheet? Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!