Formula: calculate percentage based on certain criteria

Hi, please can you assist?
I have 2 worksheets, called "Position Requisition" and "Dashboard Data".
In the "Position Requisition" I have two columns, called "Total Train ATTR" and "Location"
In the "Dashboard Data" I have two columns, called "Primary Column" and "Totals"
I want to add up all the totals in the "Total Train ATTR" column that are associated with a location in the "Location" column and I want that total displayed as a percentage in my "Dashboard Data" sheet.
So if I have 6 Asheville's in "Locations" that totals up to 0.69 in "Total Train ATTR", then I want that to display 11.5% in my "Dashboard Data" sheet.
Answers
-
Hi @Desire
You can use a SUMIFS Function to look into a source sheet and sum values based on a criteria. For example, you could write something like this in your Dashboard Data "Totals" column:
=SUMIFS({Total Train ATTR column}, {Location Column}, [Primary Column]@row)
You'll want to create two cross-sheet {references} to look at your two columns in the other sheet, see: Create cross sheet references to work with data in another sheet
Then once you have all the totals, you can use this Dashboard Data sheet as a source for a Pie Chart in your Dashboard. This will automatically create a percentage based on the values for each row compared to the total of all rows.
Let me know if that works for you!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thank you so much. I had the correct formula, I just didn't think of using a Pie Chart, it works perfectly, thank you.
-
No problem! Glad I could help 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!