Summary Counts
With the above chart, I want to do a summary data that I could possible chart.
You have the Project Lead, Then I want a count by Project Type for each lead.
So looking for something like this.
Project Lead Compliance Security Project, etc
Jose Garcia 10 5 4
How would I get this done
Best Answers
-
I can see that you have two {cross sheet references}. Have you checked that both are looking at the correct columns in the other sheet?
To check this, click on {Project Lead}, then Edit Reference and make sure you've clicked on the correct column name in the other sheet.
If when you click on it, the helper pop-up only says "Reference Another Sheet" this is because the {Project Lead} is only recognized as text and is not associated with another sheet.
You'll need to delete {the reference} and click Reference Another Sheet to make the pop-up window show where you can then select the correct sheet/column.
However if the blue hyperlink below says Edit Reference, then when you click on it a pop-up window will appear allowing to you select the correct columns.
Does that make sense?
If you still need help, please provide the following:
- Confirm what error you're getting
- Screen capture of the {Type} or {Data2} reference pop-up window showing the column
- Screen capture of the {Project Lead} reference pop-up window showing the column
- The formula that's being used in the other sheet in either of the two columns, or if no formulas, the column-type for each column and how the data is input (e.g. a Form, manually in the sheet, and so on).
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
First off, Genevieve, Thank you for taking a look at this one. Your time on as well as others was very much appreciated. I was missing the second reference. Once I created it and did some manual verifications, It seem to work so I extended it to the other columns and spotted check some numbers manually. I am not the greatest when it comes to formulas as math has not been my strong set in this area. This will help a lot for my project portfolio management dashboard I am crafting. Thank you again
-
I'm so glad to hear you were able to get it working, well-done! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Would suggest the addition of a helper sheet to run those numbers, and based on the parameters you set, the metric / helper sheet will auto update. I've attached a version of this that I run for our market research projects, along with how the formula is set up to get you started. Where I have the Category Labels, I would suggest you putting your team members names, and then in the sections below you can create labels of any desired datapoint. This information makes things easily chartable by person, or across larger categories.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
-
not sure if I follow your process.
I want to be able to count by person the types of projects they are working on then chart that
I can't seem to follow doing that in your answer
-
I have the following helper sheet setup, but starting with Jose on the formula, I am getting unparsable
-
@Buster Davis I think your formula is not referencing the project lead column that's in the other sheet.
=countifs(collect({lead type},{type},$type@row,{project lead},{catergory1}$1))
-
So lets take a step back on this one because the above formula is not working.
The data sheet layout is below: it shows the two columns.
Here is the helper sheet and here is the formula
=countifs(collect{Lead Type}({type},$type@row,{project lead},{catergory1}$1)) and it is giving me the parse error
In this sheet, it has a reference to the data sheet:
and this reference is pointing to the two columns in master data sheet:
So going back to the helper sheet - Project Lead Category, I want to take the lead, and do a count of project by type they are currently working on.
Tell me what I am doing wrong or need corrected.
-
Just try a basic COUNTIFS. No need for a COUNT/COLLECT or COUNTIFS/COLLECT.
=COUNTIFS({Type}, $Type@row, {Project Lead}, [Catergory1]$1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul, i just copied your formula and pasted it on the sheet and still the same output.
-
Hey @Buster Davis
Just hopping in here to clarify! You won't be able to copy/paste {references} unless you've already built them out in your sheet with that exact name.
I'd recommend re-writing Paul's exact formula in your sheet directly, ensuring that you're creating the {cross sheet references} pointing at the correct columns. Note that Paul's formula uses two separate references, one to each column. (Whereas in your screen captures you have one reference across two columns).
Let us know if that helped!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
It's just the formula that i copied and paste. I had done this before with no issues. Under manage references, I did create the link. could it be something else?
-
Did you try writing it out in your sheet instead of copy/pasting? This will ensure Column Names are spelled correctly as well. For example, Paul's formula says [Catergory1]$1 but it looks like your sheet is [Category 1] with a space.
If this hasn't helped, can you post a screen capture with the formula open in the cell?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you,
I did find category spelled wrong and I corrected that.. I believe we are getting closer now as now it my cell message is saying invalid ref.
Not sure how that can be as I have the reference created above
-
Try retyping the formula from scratch and using the steps outlined below to create the cross sheet references as you type out the formula.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
any further help on this one.. still needing some assistance
-
It sounds like you may have typed the words {Type} into your formula (or copy/pasted them into your sheet) instead of selecting a column to reference on your second sheet while building the formula - could that be correct?
If so, here's another Community post where I explained how to create {these} references, step-by-step. This, along with Paul's article linked above, should help you build your formula.
If that still hasn't helped - do you have any formulas in your {Type} column in the other sheet or the {Project Lead} column in that other sheet?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you,
this is what I have for the formula currently. Thoughts
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!