Cash Flow Workbook- Is SUMIFS the best function?

[konfusion9]

Hello, I am trying to create a simple cash flow worksheet to manage my personal finances. I am having trouble writing the correct formula to calculate the sum of my weekly expenses (column J).

If the expense due date (B41:B48) falls within the the date range for the week (example- A4:G4), then sum the amount due (C41:c48) and return the value in column J. I'm not sure why I'm struggling with this- brain is fuzzy. Any help is appreciated! Thank you.

-Chris

[Limor_OP]

Hi,
1. Your formula is referencing A4 whule it's an empty cell.
2. The parts in red are redundant.

=SUMPRODUCT(SUMIFS($C$41:$C$48,$B$41:$B$48,$B$41:$B$48>=A4,$B$41:$B$48,$B$41:$B$48<=G4))

[konfusion9]

Thank you! I get a formula error when I remove the ranges you highlighted in red, so I had to put >=A4 in quotes. A4 is blank for September, but the formula should reference column A all the way down.

[Limor_OP]

Please explain what exactly are you trying to do.

[konfusion9]

In column J, I am trying to total the sum of the expenses in C41:C48 if the due date (B41:B48) falls within the range of the week (example- A4:G4). Did I explain that well?

[Limor_OP]

Try this in J4 and down:
=SUMIFS($C$41:$C$48,$B$41:$B$48,">"&A4,$B$41:$B$48,"<"&G4)

**Instead of the blank cell - put a zero.

[konfusion9]

That worked! Thank you so much for your help.

[Limor_OP]

Put 0 in A4, dont leave it blank

[konfusion9]

Sorry to bring this up again. This formula works, except for toward the end of the month when a range restarts at the 1st of the month. Is there a better formula that will return a value if it exists in a range that isn't sequential? see attached. thank you!

[Limor_OP]

Hi
please post the example in excel file, where the problem is showing.

[konfusion9]

I attached a new workbook to this thread. Row 9, for example, isn't picking up the values for payments due the 1, 2 and 3 of the month since those values are not greater than A8.

[Limor_OP]

Hi
Please also add manually the results you are expecting to see.Thanks

[konfusion9]

Thank you! I manually added the expected results to cell J9.

[Limor_OP]

OK. you can use the sumproduct in J4 and down:

=SUMPRODUCT(($A4:$G4=$B$41:$B$48)*($C$41:$C$48)*1)

Let me know if that works for you...

[konfusion9]

That worked! Thank you so much for the help. Have a nice day!