Customer Summary Bills Ageing Report Through Excel Formula

[Muh]

Dear Experts
Kindly Create a Excel Formula In Given Answer table For Customer Ageing report.

1.1st Sheet Outstanding Bills of a customer dealing with a different branch of a group.

2. Different Branches are Dealing With Same Customer

3. Sample Answer Format are Given Sheet-2 "Sample-Answer"

4. Answer required Sheet-3 "Answer" through Formula.

5. Customer Balance and Ageing Data Required Based On Branch Code

6.By doing a Customer Different Branch's Deal. Account Credit Days & Limit .In case of same customer, just Came Account Credit Days & Limit against a 1 branch . "Colum K and L"

7.Refer "Sample Answer-Sheet" Table 8 The Customer Balance Zero . If Customer Balance Zero Cam Balance Amount Colum Then No Need to Came Ageing Amount Ageing Days in Colum

 

[shamimarita]

Hello Muh,

To calculate the aging report from Sheet 1, you can use the following formulas:

Balance Amount:
=SUMIFS(C2:C10, B2:B10, "DRAGON PVT LTD")
To get the balance per customer, change the ranges for other customers.

Aging buckets:
For <30 Days:
=SUMIFS(C2:C10, B2:B10, "DRAGON PVT LTD", D210, ">="&TODAY()-30, D210, "<"&TODAY())

For 31-60 Days:
=SUMIFS(C2:C10, B2:B10, "DRAGON PVT LTD", D210, ">="&TODAY()-60, D210, "<"&TODAY()-30)
Similarly, by updating the date conditions, you can adjust this for other date ranges like 61-90 Days, 91-180 Days, and >180 Days.

 

[Muh]

Thanks