# Making Monthly Sales Pivot Report Using Excel Formulas Only.

Aug 22 2022
Closed

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.

Community Manager
Status changed to: Closed

Community Manager

Thanks for the suggestion!

However, the idea space you have posted in is specifically for ideas for the Microsoft Tech Community website, not for products and services such as Microsoft Excel. Sorry about that.

We'd recommend you post this suggestion here instead: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472

Similar Ideas
No similar ideas