问题描述:

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

相关阅读:
Top