So I have a monthly budget in Excel that I'm using to keep track of making sure all of my recurring bills get paid on time.
Let's assume I have a table formatted like this:
Bill | Due Date | Amount Projected | Actual Amount | Diff
Mortgage | 1st | $1,500 | $1,510 | -$10
Water Bill | 13th | $30 | $25.80 | $4.20
I get paid on the 1st and the 15th, and I'm trying to get two rows that give a sum of all of the bills that are due within the first paycheck, and a sum of those that are due after.
So I'm trying to use the SUMIF function, however because the DUE DATE column is text and not date so that I get the 1st, 2nd, etc, I'm not sure how to run a comparison on this.
So I have the formula to get rid of the th, nd, etc as:
=LEFT(B2, LEN(B2)-2)
If it didn't have the th, or nd, the SUMIF function would look like this:
=SUMIF(C2:C3, "> 15", B2:B3)
But I just can't figure out how to combine the two formula. Is there any special character that runs the transform on the cell where I can pass that into the formula, without having an intermediate cell somewhere to just hold the 1, or 13 for the date due?
I know there are other ways to do this like just sorting the columns by due date and then only selecting that range, but I'd like to not have to manually change a bunch of other "reporting" columns every time I add or remove a bill.
Thanks for any help in advance.
