Search code examples
databaseexcelspreadsheetfinancerecurring-billing

SUM of bills due within a Table

On my previous question, I asked how to automatically tell me when my next bills are due based on today's date. Here is the link: Display the upcoming due date.

Once I have these bills compiled, let's say I have a series of bills in table array A2:C4, and in this series I include the type, the amount due, and the due date. I need to learn how to generate a formula that shows me the sum of bills due between pay dates, listed in B6:D6.

I have an image that show a bit of what I am trying to accomplish.

enter image description here



What I want to do is in Cell C8 I need to have the formula for the bills whose dates are between Cells B6 and C6. The only bill included should be the Visa, so $25 due. However, once I get paid, the next pay date will be 11/6/15, and I will need to pay the Amex bill and the MasterCard bill, so it will need to show $50 in cell C8 then. I know that there is a way to do this, but my brain hurts when I try to figure it out.

Solution

  • Try this formula:

    =SUMIFS($B$2:$B$4,$C$2:$C$4,">="&B6,C2:C4,"<"&C6)

    It's a SumIfs formula that checks to see if your date is "greater than or equal to" B6, and "less than to C6", meaning your date is on or after, October 23, but on or before Nov. 6 (as you have the dates in the screenshot).

    edit: re-reading your question, I am slightly confused I think, but let me know how this works and what I need to change/update.