Mimic a Pivot Table, Group by date and SUM

jdahl

It's the Row@row for the IF statement being true that is doing it. It's just taking the value for the ROW column when the IF statement is true rather than incrementing the counting from 1 : N

Paul Newcome

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)

jdahl

Thanks a lot for your help, seems to be working now. I'll just need to wrap some IF statements around the Row ID and Row columns in the pivot so they don't show Invalid Value errors. For completeness if someone else ever sees this, this is what I have in the Pivot sheet

Row ID column with sequence of numbers 1:N

Row column with

=SMALL({Data Sheet Rank Column}, [Row ID]@row)

Dates Column with

=INDEX({Data Sheet DateNumber Column}, MATCH(Row@row, {Data Sheet Rank Column}, 0))

Paul Newcome

@jdahl I have found that it is usually easier in cases like this to just use an IFERROR instead of an IF.

=IFERROR(original_formula, "")

jdahl

Thanks for the tip, definitely made it easier.

Of course it then made all the values in the dates column of the pivot table sheet with blank ranks all be the same date. This one I wrapped in an IF looking for ISBLANK in the rank column.

jdahl

So, ran into a new problem now that I have the SUMIFS in the pivot table. I have some rows that are Parents that sum from children. So now my SUMIFS also sum those parents because they have the matching date in them. Is there a way to skip those Parent rows, or do I need another helper column that has only the dates from the rows I'm interested in summing.

I'm guessing the latter?

I found your suggestion on another post but between sheets I'm not seeing how to implement it.

=IF(COUNT(CHILDREN()) = 0, original_formula)

jdahl

https://community.smartsheet.com/discussion/comment/297950#Comment_297950

So, ran into a new problem now that I have the SUMIFS in the pivot table. I have some rows that are Parents that sum from children. So now my SUMIFS also sum those parents because they have the matching date in them. Is there a way to skip those Parent rows, or do I need another helper column that has only the dates from the rows I'm interested in summing.

I'm guessing the latter?

I found your suggestion on another post but between sheets I'm not seeing how to implement it.

=IF(COUNT(CHILDREN()) = 0, original_formula)

Paul Newcome

@jdahl We can't use hierarchy functions with cross sheet references. You will need to insert a helper column on the sheet containing the source data that flags which rows should be summed and then include this helper column as an additional range/criteria set you in your SUMIFS.