Countif and Sums

Options

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

Tags:

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    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
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!