问题描述:

I am working on a personal budget sheet in excel, and it's formatted based on my pay dates, to provide more drilled-down information. I have attached an example of it below for reference.

I would like to put a formula into `J2`

, `J3`

, and `J4`

which will take the data in cells `C9:C26`

and `H9:H16`

, match it to the date in cells `D2:D4`

, then subtract the expenses in `D9:D26`

and `I9:I16`

from `E2`

, `E3`

, and `E4`

.

As you can see, I have just individually summed the cells; however, I would like a formula to be able to adjust as I change the value in cells `C9:C26`

and `H9:H16`

.

I have found that I can do it with ONE cell, but not multiple or a range. This is the formula I used, and I cannot find a way to make it apply to the entire range of cells: `=IF(C14=D3,E3-D14)`

I've also tried: `=IF(C9:C25=D3,E3-D9:D25)`

-- I know this formula doesn't work and why. I cannot figure out how to get column C to correspond with column D.

The Budget Sheet

You just need to use `SumIf()`

.

In cell J2, put this formula: `=SumIf($C$9:$C$25,$I2,$D$9:$D$25)+SumIf($H$9:$H$25,$I2,$I$9:$I$25)`

and drag down the three cells.

With that, you can add `E2-[formula]`

to subtract all that from `E2`

. Or of course, just do `e2-J2`

instead. I think that should do what you're looking for. If it's not quite it, let me know and I can tweak.

If you plan to have more than 1 criteria go with SUMIFS Yes, with S