I need help to sum all data by month & owner in one column for a pivot table for our dashboard.


This is our raw data sheet. For the pivot table chart I need to sum the location total for each owner. by month into one single column in my pivot table sheet. The pivot table sheet will then display on our dashboard. And the pivot table is not an option for us right this moment.

Here is our pivot table chart so you have a reference. Column 9 is where I need to put the data.



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Angie F

    If your license gives you access to the Pivot app...

    Depending on the rest of the data in your sheet, you might be able to go directly from your source sheet to the pivot app but I suggest first building a report that has all the sheet columns in it. If you want to filter the data for the pivot table, the report will allow you to do that. For instance, next year you may want to exclude 2023 data. (You would have to add a Year column to your source sheet then include that filter in the report to do this). My personal preference is to always link a pivot to a report vs a sheet to have filtering options later, if needed.

    Either using the source sheet directly or the report:

    Add the Owner as your Pivot Row, the Month as your Pivot Column and the Location (make sure it's SUM) as your Value. After executing the Pivot the app will generate a Column for each distinct month number that shows up in your data. Assuming Jan-Sep are all represented in your data, Sept will be column 9.

    Is this what you needed?


    You may find this article useful

  • Angie F
    Angie F ✭✭

    We don't have the pivot app and we will be building a new sheet for each year. I need to know how to do it without using the pivot table app. I will take a closer look at building a report as they may offer some assistance. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!