Hello, How do I add a 3rd column to create a metric sheet.

Options

The left column is week. The horizontal is the site. I want to pull a summary of the weekly booking for each site. Below is the sheet I am attempting to pull the data.


Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    Hey, Mark. I did this assuming that you have two separate sheets - one for tracking each week's site visit volume by provider and week, then the other (the one in lilac) for aggregating the weekly site visit volume by site.

    On the lilac sheet (Weekly Site Summary), you can use this formula in each of the "zero" cells for the sites:

    =SUMIFS({Site Visits Patients Booked}, {Site Visits Site}, "MTZ", {Site Visits Week}, Week@row)

    NOTE: You would need to change the name of each site (the bolded letters above) to correspond with the correct site. So, for example, =SUMIFS({Site Visits Patients Booked}, {Site Visits Site}, "SHA", {Site Visits Week}, Week@row)

    See images below for how I tested. The top sheet is basically your lilac sheet, but slightly rearranged. The bottom sheet is a condensed version of your site visit detail sheet.

    The cross-reference {Site Visits Patients Booked} is the [Number Patients Booked] column on the bottom sheet. The cross-reference {Site Visits Site} is the [Site] column on the bottom sheet. And the {Site Visits Week} cross-reference is to the [Week] column on the bottom sheet.


    Just in case cross-reference formulas are new to you, here's a good tutorial: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

  • Mark Buell
    Options

    Thank You, it took a couple of attempts with he end of the line "Week@row" but got it to work. Thank You!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!