May 18 2022 03:37 PM
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.
May 19 2022 01:36 AM
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.
May 19 2022 10:29 AM
May 20 2022 12:00 AM
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 ,
May 20 2022 10:00 PM - edited May 20 2022 10:01 PM
@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"))
May 23 2022 02:27 AM
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))