Excel Need aid in Excel Formulas

[Blonde Heather]

Hey everybody! I recently lost a laptop & everything on it. It has been so many years since I made my billing statements that I can't remember how I made the formulas for the invoice totals at the bottom. I thought I had them right, but they just didn't get it done. Lol.

One formula was SUMIF the balances in the H column if the date in the A column was between 0-30 days
The other formula was SUMIF the balances in the H column if the date in the A column were between 31 & 60 days
And SUMIF the balances in the H column if the date in the A column were over 60 days

Can anybody help me? I'll be so grateful I will sing your praises aloud over here in my hole!

Thanks everybody even if you just read
-BH

 

[Becky]

Welcome to the forum! Sorry to hear about your laptop

So you are looking for separate totals dependent on when the money is due (less than 30 days, 31-60, 60+)?

The easiest way for you to do this would be to add an extra column for the number of days until due. Let's say this is column B, after the due date in column A. The formula for number of days until due would simply be =A2-TODAY() (assuming the due dates start at A2 - change it if not). Make sure the formatting is set to number or it will look wrong!

Your SUMIF fuction for 30 days or less would be =SUMIF(B2:B10,"<=30",H2:H10) where B2 to B10 is the range of days until due, and H2 to H10 are the corresponding values.

Your SUMIF function for more than 60 days until due would be =SUMIF(B2:B10,">60",H2:H10)

As there are two conditions for between 30 and 60 days (ie it is more than 30 days, and less than 60 days) we need to use the SUMIFS formula for this one. =SUMIFS(H2:H10,B2:B10,">30",B2:B10,"<60")

Hope this helps, but let me know if I haven't understood what you're are asking for!

 

[Blonde Heather]

Thank you reading & understanding the loss of a computer. It was like losing a friend.
Thankfully, I have all my important and personal stuff on external drives so I pretty much loss stuff like this.

I didn't have an age column. I didn't want one for the customers either. Lol. So, I trudged through. I am posting this though for others who might want the help in the future. Because no where I found online had this crappy formula, but to me is priceless! I tell you what though, this wasn't like riding a bike. It makes me want to take a beginners course in Excel lol.

Column A is the date of the invoices in my scenario
Invoice Balance was Column H
(Both starting at Row 9 & ending Row 36)

Invoice Total that were 0-30 days =SUMIFS(H9:H36,A9:A36,"<"&TODAY()-0,A9:A36,">"&TODAY()-31)
Invoice Total that were 31-60 days =SUMIFS(H9:H36,A9:A36,"<"&TODAY()-30,A9:A36,">"&TODAY()-61)
Invoice Totals that were 61-90 days =SUMIFS(H9:H36,A9:A36,"<"&TODAY()-60,A9:A36,">"&TODAY()-91)
Invoice Totals that were 91+ days =SUMIF(A9:A36,"<"&TODAY()-91,H9:H36)

I tried to add a copy, but the upload a file wouldn't add the file type If you want, you can message and I can send one...

Enjoy & Happy computing!
BH

 

[Becky]

Nicely done! Thanks for sharing your solution