Want to count number of instances in a column

Rene Montijn

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?

Rene

Paul Newcome

Then you should be able to use something like this (using the appropriate methods for creating cross sheet references).

=COUNTIFS({Other Sheet Workstream Column}, "Stream1", {Other Sheet Category Column}, "Action", {Other Sheet Status Column}, "Open")

Of course you will need to either change the bold portions to suit your needs or you could use cell references to allow for dragfilling of the formula across/down a table depending on your setup.

handmadetsunami

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

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

Hope this helps

Rene Montijn

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.

Rene

Rene Montijn

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

handmadetsunami

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

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

Rene Montijn

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.

Rene

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")

Rene Montijn

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")

Rene

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".

Rene Montijn

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

Etc.

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

Hope this helps.

Rene

Paul Newcome

Then you should be able to use something like this (using the appropriate methods for creating cross sheet references).

=COUNTIFS({Other Sheet Workstream Column}, "Stream1", {Other Sheet Category Column}, "Action", {Other Sheet Status Column}, "Open")

Of course you will need to either change the bold portions to suit your needs or you could use cell references to allow for dragfilling of the formula across/down a table depending on your setup.

Rene Montijn

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.

Rene

Paul Newcome

Happy to help! 👍️

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

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

https://community.smartsheet.com/discussion/comment/235833#Comment_235833

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

Paul Newcome

@Kimbh Here is an article regarding cross sheet formulas:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas