Excel formula Aging Report

[analytical_ang]

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 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.

Name  Aging   Recency    31   61   90  120

A           31         40                  $10

B            10        60          $11  

C           55         30                          $10                         

D          31          46                   $75

E          30          30          $1 

THANK you for your help! I cannot download files or attachments. 

[#2]

Hi @analytical_ang 

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.

[#3]

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.

[#4]

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 ,

[#5]

@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")) 

[#6]

Hi @analytical_ang 

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))