Forum Discussion
Excel formula Aging Report
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"))
the LEFT function is only relevant if you want to get a certain left part of a text (like the 1st character of a name). It's not to be used with numbers.
Based on your screenshot, your formula should be
=SUMPRODUCT(X$2:AB$2577*($S$2:$S$2577<=31)*(LEFT($N$2:$N$2577;1)<="G")*($T$2:$T$2577>=45))
