setting up a PO Tracking WB - Help with Formuli

Hello all,

I'm needing help with some calculations (and thinking through what I'm trying to do).

in the attached WB you can see that I'm trying to setup a Purchase Order tracking worksheet which will give me the balance of Budget - Total Payments. The Total Payments is a sumifs, and is based on the name that is entered into name column. however the total sum, must match the current fiscal year (july 1, this year - June 30, following year) of the date which is put into the date field (this has me stumped).

The "Remaining Balance" column is the key indicator for the admin person tracking this information. It is based on:

1. sum of payments made to the person in the name field.
2. date column must be between the dates for the current fiscal year.
3. Match the Location field.
4. subtraced from the budget amount for that person (viewed on the LIST ws)

The Remaining Balance will have a color symbol from conditional formatting applied based on the remaining balance (the closer to '0' (from the budgeted amount) the symbol and color will change from Green to yellow - to red).

Any direction or help with calculations will be greatly appreciated.

Thanks

David

Sorry for the Double entry...

However, In playing with this WB, it seems that changing the data sheet to a Table serves one of my purposes well. At this point, I think I'm just needing to get the formula in the last column "REMAINING BALANCE" correct.

I'd like to also make sure that the record date is within the current fiscal year.

I've attached a revised version of the previously posted WB