Mimic a Pivot Table, Group by date and SUM

jdahl
jdahl ✭✭
in Formulas and Functions

I'm trying to figure out how to essentially mimic a pivot table using functions. And no we won't be getting the pivot table app hence needing this solution.

I want to group items from a sheet by matching dates in a column, and SUM the contents of a few other columns based on those matching dates. The SUMIFS is part of it I think but I can not figure out the group by date part. It's straight forward in a pivot table.

I tried to do this in a report but unfortunately the graphs on the dashboard don't allow you to plot based on the sums in the report, otherwise that is a nice way to also do this. I'll attach some images of what I'm working with.

The pivot table version just shows what I'm after if you did this in excel with a pivot table.

The deliver table just shows a snippet from the sheet I want to create this from. I want to group by the ship column, and then for all matching dates sum the values for the demand, planned, and shipped columns.

So for the 5/5/2021 I would sum 2 rows and have 6, 6, 6 etc.

I also just added an image of the report just to show I can get close to what I want, if only there was the ability to graph using the sums of the different columns

report example.png


deliver table.png


pivot table version.png


Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The reason I ask is because the "table sheet" where we house the pivot table will need to be "prefilled" with enough rows to accommodate as many as you will need. So if you think you won't have more than 50, then I suggest prefilling to 75 just to be on the safe side. That kind of thing. I will point out where the prefilling comes into play.


    On the source sheet we are going to insert 4 columns.


    1. Auto-Number column called "Auto" with no special formatting.
    2. Text/Number column called "Row" with the following column formula:
      1. =MATCH(Auto@row, Auto:Auto, 0)
    3. Text/Number column called "DateNumber" with the following column formula:
      1. =VALUE(YEAR(Ship@row) + "" + IF(MONTH(Ship@row) < 10, "0") + MONTH(Ship@row) + "" + IF(DAY(Ship@row) < 10, "0") + DAY(Ship@row))
    4. Text/Number column called "Rank" with the following column formula:
      1. =IF(COUNTIFS(DateNumber:DateNumber, DateNumber@row, Row:Row, @cell <= Row@row) = 1, RANKEQ(DateNumber@row, DateNumber:DateNumber, 1))


    Now we go to your pivot sheet and prefill as many rows as you need starting at 1 on row 1 and going on down the line. Call this column "Number"

    1

    2

    3

    4

    5

    6

    7

    so on and so forth until you have more than enough rows populated to cover the number of unique dates.


    Then we put this column formula in your date type column:

    =INDEX({Source Sheet Ship Column}, MATCH(Number@row, {Source Sheet Rank Column}, 0))


    Now that you have your dates populated, you can use a SUMIFS

    =SUMIFS({Source Sheet Range To Sum}, {Source Sheet Ship Column}, [Date Column]@row)

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. We are closer. Go back to the pivot table sheet and insert a text/number column called "Row" and enter this formula:

    =SMALL({Data Sheet Rank Column}, Number@row)


    Then update the formula in the date column to look for this value instead of the value in the Number column.

    =INDEX({Source Sheet Ship Column}, Row@row)

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
«1

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!