Data summation with multiple conditions

Hi all, hope I can get some help here.

I am trying to create a rolling balance for an account assuming known Weekly and Monthly expenses. For the weekly expenses, I would know which day of the week it would occur (1-7) and for the monthly expenses I would know which day of the month it would occur (1-31). I would start with the current date using TODAY() and add consecutive dates for the next 180 days. I would then like excel to look at all the expenses, look at a field that calls the expense out as weekly or monthly, then look at the date in the data table, and determine if today is one of those days (using WEEKDAY() and DAY()). I could then see this for the next 180 days to get a good estimate of what the Account balance should look like.

I can get everything to work expect for excel knowing how to distinguish between weekly or monthly expenses. I have tried playing around with SUMIF, DSUM, and SUMPRODUCT but I can't get that final piece to work. Below is a simplified version of what I am envisioning this looking like.

Capture.JPG

The SUMIF I currently have in cell C4 is: =SUMIF($G$4:$G$8,WEEKDAY(A4),$H$4:$H$8)
The formula for C5 reads =SUMIF($G$4:$G$8,WEEKDAY(A5),$H$4:$H$8), etc, etc.

The SUMIF I currently have in cell D4 is: =SUMIF($G$4:$G$8,DAY(A4),$H$4:$H$8)
The formula for D5 reads =SUMIF($G$4:$G$8,DAY(A5),$H$4:$H$8)

Right now it can't recognize if it's a weekly or monthly expense. If it could, based on the Data Table, the only value that should be in column D is the $50 at cell D16.

Any idea on how to do this? I think DSUM might be what I need but I can't figure out how to do variable criteria to deal with the constantly changing date.

I have attached this sample excel file if you would like to play around with it.

Thanks for the help! Didn't know about SUMIFS

So this works great for weekly and monthly and I thought I could figure out how to use this for Bi-weekly (every 14 days) but I seem to be stumped again .

Let's assume instead of weekly, I replace that with Bi-weekly. I thought the equation would just be =SUMIFS($I$4:$I$8,H$4:$H$8,MOD($B4,14),$L$4:$L$8,"Bi-Weekly"). Obviously I am missing something because it just returns $0. Thoughts on what is wrong?

Still no dice. DAY() returns the day of the month, which isn't what I would want in this situation, correct?

Some more background, I was previously using this formula

Formula:

Please Login or Register  to view this content.

(modified to make sense with the above cells). This only works though when it is identifying one specific cell, not a range like the above SUMIFS do. I would like to apply what you helped me with earlier except now using the BI-WEEKLY as another option.

Thanks for all your help.

Because of how DAY() works, it looses count of the 14 days every time a new month starts. I would need this to be accurate for 180 days. If you can't call out a full date (day/month/year) this won't work. You can see what I mean if, using your attached spreadsheet, you make a bi-weekly entry and input "1" under the "Date" column, you will get an entry on 6/29 and then again 7/1 (2 days later, not 14). Below is an image of this.
Capture.JPG

This is exactly what I wanted. It looks like it works so far so I should be set. Thanks for the help!