COUNT(COLLECT) assistance please

RobH

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?

Kelly Moore

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

RobH

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 :(

RobH

I just tried again and at least got a number as a reult...

I feel I am close with this one...

Kelly Moore

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.

RobH

Hi Kelly,

Unfortunately no. It seems to give a consistent error of [Total Column]+80???

Cannot for the life of me understand why.

Kelly Moore

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.

RobH

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.

Kelly Moore

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.