Forum Discussion

analytical_ang's avatar
analytical_ang
Copper Contributor
May 18, 2022

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...
excel
Formulas and Functions
Martin_Weiss's avatar
Martin_Weiss
Bronze Contributor
May 20, 2022

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 ,

 

analytical_ang's avatar
analytical_ang
Copper Contributor
May 20, 2022

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

 

 

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    May 23, 2022

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

Resources