I'm starting a new monthly task where I'll break down how much is owed (column G) for my loan office's accounts according to date made (column AE) and how far past due they are (column I). I need to set the past due ranges to be >= 30, 31 to 60, 61 to 90, and over 90 days.
So, given the certain date at the end of the formula, I have this for >=30:
=SUMIFS(G1:G10, I1:I10, "<=30", AE1:AE10, ">=1-1-13")
this for 31 to 60
=SUMIFS(G1:G10, I1:I10, ">30",B2:B10, "<=60", AE1:AE10, ">=1-1-13")
this for 61 to 90
=SUMIFS(G1:G10, I1:I10, ">60",B2:B10, "<=90", AE1:AE10, ">=1-1-13")
& this for over 90:
=SUMIFS(G1:G10, I1:I10, ">90", AE1:AE10, ">=1-1-13")
These work fine except my range is always going to vary and I'll have to do this for each of our loan classes every month. So, it's impractical and prone to error for me to establish each individual range or fill each sheet with something like "blank" to avoid this error. I'd really like to just use this formula:
=SUMIFS(G:G, I:I, "<=30", AE:AE, ">=1-1-13")
...and have Excel ignore empty cells. Is this possible? Is there another workaround?
Thanks for any help!!
Bookmarks