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?
Rene
Best Answer
-
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.
Answers
-
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.
Rene
-
I think I have figured it out.Thank you for your help
-
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.
Rene
-
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")
Rene
-
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
Etc.
In this way I can create a dashboard with per stream the no of open items per catergory.
Hope this helps.
Rene
-
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.
-
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
-
Happy to help! 👍️
Please don't forget to mark an "Accepted Answer" so that others know a solution has been found.
-
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","") -
Hi, how can you do this when counting from another sheet? Thank you
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 412 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 140 Just for fun
- 57 Community Job Board
- 460 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
