Excel formula Aging Report

Question

Hi there-&nbsp;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 that meet the following criteria: "recency of 45 days or less" (column c) AND aging days of 31 or more" (column b)If this formula was used in the example below, the total would be $20.&nbsp;Name&nbsp; Aging&nbsp; &nbsp;Recency&nbsp; &nbsp; 31&nbsp; &nbsp;61&nbsp; &nbsp;90&nbsp; 120A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; 60&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $11&nbsp;&nbsp;C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;55&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 31&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 46&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$75E&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp;&nbsp;THANK you for your help! I cannot download files or attachments.&nbsp;&nbsp;&nbsp;

martin_weiss · Answer

Hi&nbsp;analytical_ang&nbsp;&nbsp;it's not clear to me what you mean with "if last name is less or equal than H".&nbsp;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.

analytical_ang · Answer

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-HAging days 31+Recency days under 45 daysSo 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.

martin_weiss · Answer

Hi&nbsp;analytical_ang&nbsp;,&nbsp;thanks, now it's clear to me. You could try the following one:&nbsp;=SUMPRODUCT((D2:G10)*(B2:B10&gt;=31)*(C2:C10&lt;=45)*(LEFT(A2:A10;1)&gt;="B")*(LEFT(A2:A10;1)&lt;="D"))&nbsp;Depending on your local regional settings, you might need to replace ; with ,&nbsp;

analytical_ang · Answer

Martin_Weiss&nbsp;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&nbsp;=SUMPRODUCT(X$2:AB$2577*(LEFT($S$2:$S$2577)&lt;="31")*(LEFT($N$2:$N$2577)&lt;="G")*(LEFT($T$2:$T$2577)&gt;="45"))&nbsp;&nbsp;&nbsp;

martin_weiss · Answer

Hi&nbsp;analytical_ang&nbsp;&nbsp;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.&nbsp;Based on your screenshot, your formula should be&nbsp;=SUMPRODUCT(X$2:AB$2577*($S$2:$S$2577&lt;=31)*(LEFT($N$2:$N$2577;1)&lt;="G")*($T$2:$T$2577&gt;=45))

Forum Discussion

Excel formula Aging Report

Share

Resources