Countif and Sums
I am a beginner in Smartsheet and have formula phobia but have been tasked with generating metrics etc. I have an ongoing intake sheet that collects details from volunteers on how much they get paid. Some volunteers donate money back so there are several metrics I have to report on.
Here are the columns I have set up.
I have to report on amount due before donation, donation amount and amount due to participant. I can create a report for each of these but that seems cumbersome and the report would need to be updated each year as we report on fiscal years and start a new sheet for each year. My thought is to do a sheet summary for each volunteer (there are only 10 volunteers) for each item I have to report on and then report the metrics to a dashboard. But can I create a sheet summary for this scenario and if so what formula would I use. As this is an intake form the same name will appear several time. So count how many times john doe donated and what was the total amount is the plain version.
Any guidance is appreciated!
Bernie
Answers
-
Create two sheets. I call mine Data and Rollup.
Data
Rollup
The formulas in Paid Rollup and Donate Rollup:
=SUMIF({Name}, Name@row, {paid})
=SUMIF({Name}, Name@row, {donated})
For the {} values, you reference use the cross-sheet reference and select the Name column, and then the Paid or Donate column respectively.
-
Thanks, so much James. I will give this a go. I have not worked with cross-sheet reference yet, but now might be a good time to learn!
Bernie
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!