Customer Ageing Different Formulas

[mba_110]

Hi Everyone,

I am short some correct code to finalize this sheets for customer ageing

This is the sheet with full data of invoices

Customer Outstanding Report.xlsx
	A	B	C	D	E	F	G	H	I	J
6		OUTSTANDING INVOICES							REPORT DATE:	5 Sep 2024
7
8		DATE	TYPE	CUSTOMER NAME	INVOICE	AMOUNT	VAT	DUE DATE	DAYS DUE	SALESPERSON
9
10		14/09/2022	Invoice	My Skin Clinic	4617	285.00	14.25	15 Sep 2024	-9
11		29/09/2022	Invoice	My Skin Clinic	4708	272.00	13.60	29/09/2022	708
12		03/10/2022	Invoice	My Skin Clinic	4737	1,000.00	50.00	1 Oct 2024	-25
Open Invoices

Cell Formulas
Range	Formula
J6	J6	=Summary!J25
B10:B12	B10	=VLOOKUP(E10,'System Report Data'!$B$7:$I$3012,2,0)
C10:C12	C10	=VLOOKUP(E10,'System Report Data'!$B$7:$I$3012,3,0)
D10:D12	D10	=VLOOKUP(E10,'System Report Data'!$B$7:$I$3012,4,0)
E10:E516	E10	=UNIQUE(FILTER('System Report Data'!$B$7:$B$3012,'System Report Data'!$B$7:$B$3012<>""))
F10:F12	F10	=VLOOKUP(E10,'System Report Data'!$B$7:$I$3012,5,0)
G10:G12	G10	=VLOOKUP(E10,'System Report Data'!$B$7:$I$3012,6,0)
H11	H11	=IF(C11="Invoice",VLOOKUP(E11,'Due Date'!$B$6:$G$930,5,0),B11)
I10:I12	I10	=$J$6-H10+1
Dynamic array formulas.

This is the summary sheet showing the ageing and current with customer name and other information, some i already achieved the only thing is ageing by column for each customer, here if the due days are in negative formula should allocate that in "Current (Not due)" column.

Customer Outstanding Report.xlsx
	A	B	C	D	E	F	G	H	I	J	K
25		CUSTOMER AGING SUMMARY							REPORT DATE:	5 Sep 2024
26
27		S.NO	CUSTOMER NAME	TOTAL INVOICES	Current (Not Due)	0 - 30 Days	31 - 60 Days	61 - 90 Days	Above 90 Days	TOTAL DUE	%
28		1	My Skin Clinic	3	0	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
Summary

Cell Formulas
Range	Formula
J25	J25	=J6
C28:C224	C28	=UNIQUE('Open Invoices'!D10:D515)
D28	D28	=COUNTIF('Open Invoices'!$D$10:$D$515,Summary!C28)
E28:E534	E28	=SUMIF('Open Invoices'!D10:I516,'Open Invoices'!I10:I516<0,'Open Invoices'!F10:G516)
F28	F28	=SUMIF('Open Invoices'!$D$10:$G$517,C28,'Open Invoices'!#REF!)
G28	G28	=SUMIF('Open Invoices'!$D$10:$G$517,C28,'Open Invoices'!#REF!)
H28	H28	=SUMIF('Open Invoices'!$D$10:$G$517,C28,'Open Invoices'!#REF!)
I28	I28	=SUMIF('Open Invoices'!$D$10:$G$517,C28,'Open Invoices'!#REF!)
J28	J28	=SUM(F28:I28)
K28	K28	=J28/$J$229
Dynamic array formulas.

Your kind help can must appreciated.

 

[Alex Blakenburg]

Try:

=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517<0))

 

[mba_110]

Try:

=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517<0))

Many thanks. but how to calculate ageing 0-30, 31-60 and so on ?

 

[Alex Blakenburg]

SumIfs won't let you sum 2 columns at once, you would need a helper column totalling the 2 or use 2 Sumifs.
See if this works for you.

20240905 Sumifs mba_110.xlsx
	B	C	D	E	F	G	H	I
27	S.NO	CUSTOMER NAME	TOTAL INVOICES	Current (Not Due)	0 - 30 Days	31 - 60 Days	61 - 90 Days	Above 90 Days
28	1	My Skin Clinic	3	1349.25	0	0	0	285.6
Summary

Cell Formulas
Range	Formula
E28	E28	=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517<0))
F28	F28	=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517>=0)*('Open Invoices'!$I$10:$I$517<=30))
G28	G28	=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517>=31)*('Open Invoices'!$I$10:$I$517<=60))
H28	H28	=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517>=61)*('Open Invoices'!$I$10:$I$517<=90))
I28	I28	=SUM('Open Invoices'!$F$10:$G$517*('Open Invoices'!$D$10:$D$517=$C28)*('Open Invoices'!$I$10:$I$517>90))