COUNT(COLLECT) assistance please
Hi all,
I am trying to resolve a COUNT(COLLECT) formula to gather a number of instances from a range of categories by their current activity.
In my data sheet the categories are called "Treatment" and I want to know how many instances of a particular Treatment there are by the criteria of Work Activity.
I am using a roll-up sheet to gather this information and I have managed to get a total number column populated but I would like to break down this total by category (treatment) and activity...
I have tried quite a few iterations and been unsuccessful. I thought I may need to do COUNTIFS but I am not sure if this is the appropriate solution?
Answers
-
Hey @RobH
To get this data rollup by formula you will need to either add additional columns for every treatment type and calculate the counts for each work activity (either a count/collect or countifs will work in your sheet). For example if treatment = "Precinct". Depending on how many Treatment types you have, this can be painful.
=COUNTIFS({MASTER Capital Works Program Range 3}, [Primary Column]@row, {MASTER Capital Works Program Treatment Column}, "Precinct")
A report could give you this data and can be used in a dashboard. Reports are convenient ways to keep your dataset evergreen - if more categories are added to your sheet the data will automatically be added to a report. If you need help with this, let me know.
If your smartsheet license allows, you could look at using Pivot App to gather the data without any formulas. This would put the data into a sheet.
Are either of these options of interest to you?
Kelly
-
Hi Kelly, and thanks for the prompt response.
I have 10 Treatment types
and 6 Work Activity types
I went the COUNT(COLLECT route as I was using the PMO Template set as a guide which has a sheet similar to mine and which I am trying to copy...
I will look at reports and see if that will help.
I aseem to struggle with these formula whenever I try to use them :(
-
I just tried again and at least got a number as a reult...
I feel I am close with this one...
-
Hey @RobH
Did your formula above work? From a syntax view it looks correct? If you decide to use COUNTIFS, you will remove the {Project ID} from the equation and leave the rest.
-
Hi Kelly,
Unfortunately no. It seems to give a consistent error of [Total Column]+80???
Cannot for the life of me understand why.
-
Hey @RobH
Are you sure your range to other sheet is correct? Or the original range for the Total is correct? I notice that the range names have changed.
-
I think the range is correct but there must be an issue as it is not giving the expected results.
I am not sure how best to confirm if the ranges are correct.
-
Hey @RobH
To confirm your ranges -
Delete the range, one at a time, within your formula. When you do that the formula wizard will then allow you to insert a reference from a different sheet. Reinsert the range. It is not uncommon to find that there was an inadvertent error in ranges.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!