Countif and Sums

Bernadette Brady

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

James Keuning

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.

Bernadette Brady

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