Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
I have a column called "State". How can I do a count by all "State" and produce a chart? I want to avoid doing a formula for each State. Suggestion?
Can you share a screenshot? To get a unique count of each state you're going to have to create either a formula for each state or have another sheet that lists each state, and then does a count if with a cross-sheet formula to check if the column to the left is in your list.
Or are you just trying to count every row that has text in the state column? It would be helpful to have a clearer understanding of your needs.
Some of the row have State selected and some don't, I want a count by State, almost like a Pivot function in a spreadsheet.
You would need to create a table with all of the possibilities in one column (State Column) and the following formula in another column (Count Column).
=COUNTIFS({Master Sheet State Range}, [State Column]@row)
For the range you would follow the appropriate steps to create a cross sheet reference and select the column on the first sheet that houses the state selections.
The [State Column]@row portion just says to count the range if it equals whatever is in the [State Column] for whatever row the formula is sitting on.
Paul, is there a way to get a dynamic list of what states they are using so they don't have to show all 50 in the chart?
So each row represent each client and we are trying to find...
New clients will be added to the sheet as we go. Hope this clear things up.
Paul's solution here will provide you with a count for each state.
EDIT: I do remember seeing some posts about dynamic dropdowns somewhere here in the community. I imagine any solutions presented in those would give some pretty good insight into making a dynamic listing of what has been used.
Otherwise...
I haven't been able to find a way without going outside of Smartsheet. Due to SS's lack of parsing, it is A LOT more work than it is worth.
You would have to use a JOIN/COLLECT in conjunction with a helper column to pull the list of unique entries from the master sheet then use a series of MID statements in the rows going down the column to parse it out. You would have to add enough MID statements to account for all 50 rows in case all are eventually selected at least once. By the time you get that done, copy/pasting from dropdown is A LOT faster and easier.
Typically if the options are in a dropdown column, I will open the column properties, select all and copy, then paste it into a sheet. I have also done something similar in reverse to maintain alphabetical order in my dropdown lists.
I would suggest a table similar to below...
State Count Products Revenue
WV 1 2 3
MD 1 2 3
PA 1 2 3
VA 1 2 3
.
Where you see the number 1, use
=COUNTIFS({Master Sheet State Column}, State@row)
2 would be
=SUMIFS({Master Sheet Product Count Column}, {Master Sheet State Column}, State@row)
and finally 3 would be
=SUMIFS({Master Sheet Revenue Column}, {Master Sheet State Column}, State@row)
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
