I'm making somewhat of a bill tracker of my own in Google Sheets. This is snippet so far what I have.

Google Sheet Example Image

What I want to accomplish is on cell K2, you can see that if the cell to the right of the K column is not checked (aka it = FALSE), then it adds it to the total where I can see what the total amount I still need to keep in order to pay my bills.

Below is the formula being used for K2

=SUMIF(L5:L,FALSE,K5:K)

Now, I do have a couple of different accounts myself, so it would be useful to know which bills are taken out of what account, to get a breakdown of that too.

My problem is, I can't figure out a good way to go about this. Maybe SUMIFS is not the way to go for that. The following formula is what I tried.

=SUMIFS(K5:K, L5:L, FALSE, K5:K, "CHK 1")

Although this just returns a "$0.00" for me. I figure it's because when it checks cell K8 (as an example), it checks L8 if it is FALSE, which it is, but then it also tries to check the same K8 to see if it is equal to the string "CHK 1", but it obviously would never be correct since that is the cell with the number value I want to add.

Is there a way to make the criteria check the cell 2 spots below? I tried using...

=SUMIFS(K5:K, L5:L, FALSE, K5:K, INDIRECT(R2C0, FALSE)&"=CHK 1")

But this results in a formula parse error. I've been looking around at any other formulas that may work but I haven't found anything yet. Anyone able to help me out on this?

CodePudding user response：

try like this:

=SUMIFS(K5:K100, L5:L100, FALSE, K7:K102, "CHK 1")