Invoice Counts for a month for each customer

Seafood Dutch

New Member
Joined
Nov 27, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Thanks for that.
How about
sample.xlsx
ABCDEFGHIJKLMN
101/01/202301/02/202301/03/202301/04/202301/05/202301/06/202301/07/202301/08/202301/09/202301/10/202301/11/202301/12/2023
2C00012Customer E222000000000
3C00014Customer D224000000000
4C00015Customer C422000000000
5C00017Customer B213000000000
6C00018Customer A101000000000
7
Report
Cell Formulas
RangeFormula
A2:B6A2=SORT(UNIQUE(Table1[[Sell-to Customer No.]:[Sell-to Customer Name]]))
C1:N1C1=DATE(2023,SEQUENCE(,12),1)
C2:N6C2=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.
 
Upvote 0
Another option that will spill both down & across in C2
Excel Formula:
=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)))
 
Upvote 0
Another option that will spill both down & across in C2
Excel Formula:
=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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top