Forum Discussion
Excel formula Aging Report
Hi there- I'm in need of a formula for an aging report... What I want to ask is "If last name (column a) is less than or equal to H, then give me the balances (in columns d-g) for the accounts t...
it's not clear to me what you mean with "if last name is less or equal than H".
So in my example, the formula in B8 sums all values that meet the aging and recency criteria:
So if you could give more information about the name criteria, maybe I can provide a better solution.
- Hi there-
Essentially, what I want to pull out any account whos name begins with A-H, the total aging buckets if the meet the following criteria=
Last name begins with A-H
Aging days 31+
Recency days under 45 days
So if the last name begins with S, and is 31+ aging, and 40 days recency, it would not calculate in this formula as it doesn't begin with A-H.Hi analytical_ang ,
thanks, now it's clear to me. You could try the following one:
=SUMPRODUCT((D2:G10)*(B2:B10>=31)*(C2:C10<=45)*(LEFT(A2:A10;1)>="B")*(LEFT(A2:A10;1)<="D"))
Depending on your local regional settings, you might need to replace ; with ,
Martin_Weiss Thank you for your help. I tried your formula, and others, and still came up wonky... This is the closest one I could get, in troubleshooting exactly one cell pops up - aa5. Any idea why, or can you see what I'm doing wrong?
I used this formula =SUMPRODUCT(X$2:AB$2577*(LEFT($S$2:$S$2577)<="31")*(LEFT($N$2:$N$2577)<="G")*(LEFT($T$2:$T$2577)>="45"))
