For sales analysis, we need to make pivot report. At a glance we want to see monthly sales of each sales representative (SR). In Microsoft Excel we can do it easily by PIVOT Table. But it is really hard to make this kind of pivot table by Excel formulas. In google-sheet we can easily make it by single QUERY() function. There is no equivalent function to MS-Excel. However, we can make pivot report using new excel array manipulation formulas.
Suppose we have below sales data.
| Date | SR | Amount |
| 07/23/2022 | SR1 | $ 500.00 |
| 08/24/2022 | SR2 | $ 750.00 |
| 08/25/2022 | SR3 | $ 1,000.00 |
| 07/26/2022 | SR1 | $ 500.00 |
| 08/27/2022 | SR2 | $ 750.00 |
| 08/28/2022 | SR3 | $ 1,000.00 |
| 07/29/2022 | SR1 | $ 500.00 |
| 08/30/2022 | SR2 | $ 750.00 |
| 08/31/2022 | SR3 | $ 1,000.00 |
| 09/01/2022 | SR1 | $ 500.00 |
| 09/02/2022 | SR4 | $ 750.00 |
| 07/03/2022 | SR3 | $ 1,000.00 |
| 09/04/2022 | SR1 | $ 500.00 |
| 09/05/2022 | SR2 | $ 750.00 |
| 09/06/2022 | SR3 | $ 1,000.00 |
| 06/23/2022 | SR4 | $ 100.00 |
| 08/12/2022 | SR1 | $ 1,000.00 |
| 06/22/2022 | SR2 | $ 1,500.00 |
And we want to make below pivot report from above sales data.
| SR/Months | Sep-2022 | Aug-2022 | Jul-2022 | Jun-2022 |
| SR1 | 1000 | 1000 | 1500 | 0 |
| SR2 | 750 | 2250 | 0 | 1500 |
| SR3 | 1000 | 3000 | 1000 | 0 |
| SR4 | 750 | 0 | 0 | 100 |
So, I have used below formula to make this report.
=LET(a,TOROW(UNIQUE(SORT(BYROW(FILTER(A2:A500,A2:A500<>""),LAMBDA(x,EOMONTH(x,0))),1,-1)),1),
b,UNIQUE(TOCOL(B2:B500,1)),
c,SUMIFS(C2:C500,B2:B500,b,A2:A500,">="&DATE(YEAR(a),MONTH(a),1),A2:A500,"<="&a),
VSTACK(HSTACK("SR/Months",TEXT(a,"MMM-YYYY")),HSTACK(b,c)))
And below is LAMBDA() function to use with name manager.
=LAMBDA(sumCol,dtCol,strCol,LET(a,TOROW(UNIQUE(SORT(BYROW(FILTER(dtCol,dtCol<>""),LAMBDA(x,EOMONTH(x,0))),1,-1)),1),b,UNIQUE(TOCOL(strCol,1)),c,SUMIFS(sumCol,strCol,b,dtCol,">="&DATE(YEAR(a),MONTH(a),1),dtCol,"<="&a),VSTACK(HSTACK("SR/Months",TEXT(a,"MMM-YYYY")),HSTACK(b,c))))
I have give this lambda formula name PIVOTMONTHS. So, anyone want to use this function in any cell of entire workbook enter below formula to cell.
=PIVOTMONTHS(C2:C200,A2:A200,B2:B200)
Any better suggestion to make it improve is greatly appreciated. You can download attached sample file attached with this post.
