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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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