Invoice Counts for a month for each customer

[Seafood Dutch]

I have a table that contains, customer name, customer number, invoice number (alpha and number combination), invoice date, invoice amount and product code for each product on an invoice (this makes there to be multiple rows for each invoice).

I want to create a dynamic report that shows the months across the top, the customers down the left side and then for each month, I want to show the count of each unique invoice.

This formula gives me the count for the month: =COUNTIFS(DA2023YTD[Sell-to Customer Name],$F9,DA2023YTD[Scheduled Shipment Date],">="&G$1,DA2023YTD[Scheduled Shipment Date],"<="&G$2), but since I have each product a part of this count, the result is incorrect.

Thanks for your help!!

 

[Seafood Dutch]

Loading Google Sheets

Can you access this?

 

[Fluff]

Thanks for that.
How about

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023	01/07/2023	01/08/2023	01/09/2023	01/10/2023	01/11/2023	01/12/2023
2	C00012	Customer E	2	2	2	0	0	0	0	0	0	0	0	0
3	C00014	Customer D	2	2	4	0	0	0	0	0	0	0	0	0
4	C00015	Customer C	4	2	2	0	0	0	0	0	0	0	0	0
5	C00017	Customer B	2	1	3	0	0	0	0	0	0	0	0	0
6	C00018	Customer A	1	0	1	0	0	0	0	0	0	0	0	0
7
Report

Cell Formulas
Range	Formula
A2:B6	A2	=SORT(UNIQUE(Table1[[Sell-to Customer No.]:[Sell-to Customer Name]]))
C1:N1	C1	=DATE(2023,SEQUENCE(,12),1)
C2:N6	C2	=BYCOL(C$1#,LAMBDA(bc,IFERROR(ROWS(UNIQUE(FILTER(Table1[Document No.],(Table1[Scheduled Shipment Date]>=bc)*(Table1[Scheduled Shipment Date]<EDATE(bc,1))*(Table1[Sell-to Customer No.]=A2)))),0)))
Dynamic array formulas.

 

[Fluff]

Another option that will spill both down & across in C2

=MAKEARRAY(ROWS(A2#),COLUMNS(C1#),LAMBDA(r,c,IFERROR(ROWS(UNIQUE(FILTER(Table1[Document No.],(Table1[Scheduled Shipment Date]>=INDEX(C1#,c))*(Table1[Scheduled Shipment Date]<EDATE(INDEX(C1#,c),1))*(Table1[Sell-to Customer No.]=INDEX(A2#,r,1))))),0)))

 

[Seafood Dutch]

Another option that will spill both down & across in C2

=MAKEARRAY(ROWS(A2#),COLUMNS(C1#),LAMBDA(r,c,IFERROR(ROWS(UNIQUE(FILTER(Table1[Document No.],(Table1[Scheduled Shipment Date]>=INDEX(C1#,c))*(Table1[Scheduled Shipment Date]<EDATE(INDEX(C1#,c),1))*(Table1[Sell-to Customer No.]=INDEX(A2#,r,1))))),0)))

Fluff,

Awesome!

Your first solution worked wonderfully.

I have not dug into the second formula yet, but I will.

Thank you so much!

Seafood Dutch

 

[Fluff]

You're welcome & thanks for the feedback.