I’ve got a pivot table with names in the rows and dates in the columns. I need to display actual count per name per week so the pivot table needs to look like the following.
I then also need to carve out the names and counts for Wednesday and Thursday as shown below in the intermediary working data set. The reason it is the intermediary working data set is I haven’t been able to figure out how to dynamically combine the simple horizontal count function of summing rows M and N with the main dynamic formula in cell L5.
Cell L5 formula:
=CHOOSECOLS(VSTACK(A5:OFFSET(A5,COUNTA(A:A)-4,5)),1,MATCH(4, MOD(B4:F4,7),0)+1,MATCH(5, MOD(B4:F4,7),0)+1)
Cell O5 formula:
=SUM(M5:N5)
Cell O6 formula:
=SUM(M6:N6)
Cell O7 formula:
=SUM(M7:N7)
Finally I want to group the Wednesday/Thursday data by Count and then sort by the name as shown below in the final data set.
Cell R5 formula:
=SORTBY(L5:O7,O5:O7)
While this solution works, it is not elegant. On a week-by-week basis, I can have additional names show as in the following example.
The results for the intermediary table now look like this.
As you can see, there is no automated creation of a formula for O8 set to =SUM(M8:N8).
The final resulting data set also isn’t complete as shown here.
While I can for the intermediary data set create a whole bunch of sum formulas copied down and set to display a blank if there is no value in column L, that’s not an elegant solution and creates an extra tax on the system (I’m only using a handful of names here to illustrate the point, but the real data set is about 50,000 rows and yes this could be better served in a real database but that’s a different story).
Another issue is the final data set doesn’t dynamically expand. You can create named ranges for the intermediary data set, but you still must remember to expand them. Normally you would set the intermediary data set as a table, but that causes the dynamic formula in L5 to encounter a SPILL condition. So, you have to manually manage the final data set range to the values of the updated intermediary data set range.
What I’m really looking for here is to eliminate the intermediary data set in its entirety and have the final data set sorted by count using one dynamic formula in one cell. I haven’t been able to figure out how to do that. Also, I know that the pivot table could be refactored to just the two days of the week and a count, BUT, I also need it to display all the days of the week and a count and I don’t want to have two separate pivot tables. I would also prefer not to use VBA, just native EXCEL capabilities.
Any insights would be appreciated.
I’ve tried various solutions but have not been able to figure this out.
5
Here’s quick suggestion using the sample data below:
=LET(
weekdays_to_group_by, {4, 5},
u_name, UNIQUE(Table2[Name]),
counts_for_name, LAMBDA(acc, name,
LET(
filtered, FILTER(
N(WEEKDAY(Table2[Date]) = weekdays_to_group_by) *
Table2[Item],
(Table2[Name] = name)
),
count_for_wd, MMULT(SEQUENCE(, ROWS(filtered), 1, 0), filtered),
VSTACK(acc, HSTACK(name, count_for_wd, SUM(count_for_wd)))
)
),
counts, DROP(
REDUCE("Count for per weekday for name", u_name, counts_for_name),
1
),
VSTACK(
HSTACK("Name", "Wed", "Thu", "Total"),
SORT(counts, COLUMNS(counts))
)
)
| Name | Date | Item |
|---|---|---|
| Name1 | 9/9/24 | 0.5 |
| Name2 | 9/9/24 | 1 |
| Name3 | 9/9/24 | 1 |
| Name1 | 9/9/24 | 0.5 |
| Name1 | 9/10/24 | 1 |
| Name3 | 9/10/24 | 1 |
| Name4 | 9/10/24 | 1 |
| Name2 | 9/11/24 | 1 |
| Name1 | 9/12/24 | 1 |
| Name2 | 9/12/24 | 1 |
| Name1 | 9/13/24 | 1 |
| Name2 | 9/13/24 | 1 |
| Name3 | 9/13/24 | 1 |
3
If you have access to Pivotby, you should be able to do it in a single step:
=PIVOTBY(Table1[Name],Table1[Date],Table1[Item],SUM,0,1,2)
and
=PIVOTBY(Table1[Name],Table1[Date],Table1[Item],SUM,0,1,2,,,(WEEKDAY(Table1[Date])=4)+(WEEKDAY(Table1[Date])=5))
The slight downside of this was that I had to format the column headers to dates manually. If this was an issue, you could try:
=LET(pivot,PIVOTBY(Table1[Name],Table1[Date],Table1[Item],SUM,0,1,2),
seq,SEQUENCE(ROWS(pivot)),
IF(seq=1,TEXT(pivot,"dd/mm/yy"),pivot))