Bill Payment Tracker monthly sum question

Google Sheets
1

I’ve been using the Bill Payment Tracker from @jono with great results. Thanks!

In row 22, I tried to add a SUMIF above each month (columns H-S) to tally the Last Amount for all bills where a checkbox appeared but was unchecked, thereby displaying the remaining cashflow for that month.

I’m stymied because, while the value of H24:S is True for all cells containing a box with a check, the value is False for all cells with no check, even when no checkbox appears. Thus, a SUMIF totals all bills not checked, even when no bill is due for that month.

I’ve mined the helper data for another way to generate this sum, to no avail. Can anyone find the missing link, or suggest a workaround?

Thanks!

2

Hi Greg,

I like your idea of showing the remaining cash flow for each month. I think a way around the true/false flag can be found in the hidden column BF whose header is “Due & Not Paid”. Any month for any item that is “Due and Not Paid” has a value of TRUE.

There are twelve rows for every bill item so the formula will be a little different, but it should be doable.

Fred

1 Like
3

Hi, Fred,

That is a help, in that it does provide “due but not paid”. I would have to match the month, which I could probably figure out. However, that table contains no amount. I’d have to cobble the status and the amount from two tables. So far, that’s beyond my coding skills.

I appreciate the lead, nonetheless.

4

It’s a bit beyond my coding skills too; but, I have some time so will see what I can come up with and maybe someone else will add there two cents as well.

Fred

5

I was able to add the Est Amount to the hidden rows by adding a new column (BH) to the end and putting this formula in the header row:

={"Est Amt";ARRAYFORMULA(IF(ISTEXT(AU2:AU),VLOOKUP(AU2:AU,{C24:C,D24:D},2,FALSE),IFERROR(1/0)))}

I tried it on a copy of my sheet and it seems to work without causing any other errors.

6

Nice work @fehegner ,
That looks like the best way to solve it.

Jon

7

Thanks for the help, @fehegner !

With the Est Amt added in BH, here is the formula I added to H22, and copied across to S22, to display the sum of unchecked bills for the month:

=SUMIFS($BH$2:$BH,$BF$2:$BF,TRUE,$AV$2:$AV,H$23)

8

Glad it worked out and thanks for sharing the last piece of the puzzle.