Formula help: select a range of dates within a range of dates?

Hi everyone!

I'm completely stumped with a formula, and I hope this board could be of assistance. I've attached a sample to reference.

In column B is a list of dates, which are broken out by fiscal year (FY=7/1/ to 6/30)
Column C is a list of dollar amounts.
Cell F3 is the query ‘start date’
Cell F4 is today's date, which will be the formula: =today()

Using a date range of F3:F4, I'm trying to get each cell bordered in red to query the dates in column B for each FY; then display the matching sum from column C.

For illustrative purposes I went and manually determined what the values should be displaying as of 7/22.

Here are a couple of examples of how I am trying to get the formula to work.

Example 1
FY’08:
Start date- 7/1
End date- 7/22
Searching cells B1:B94, cells B1:B13 fall within the start and end date parameters listed above. Sum of cells C1:C13 is $45,112.00, which should display in cell F6.

Example 2
FY’09:
Start date- 7/1
End date- 12/13
Searching cells B95:B222, cells B95:B149 fall within the start and end date parameters listed above. Sum of cells C95:C149 is $150,873.03, which F7 should display in cell F7.

How to I type up this formula; its way more challenging that I first thought!

Thanks for any help!

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Better, thanks.

I'd do it like this:

Please Login or Register  to view this content.

E6 and down: Input
F6 and down: =SUMPRODUCT( ($B$1:$B$226>=G6) * ($B$1:$B$226<=H6), $C$1:$C$226)
G6 and down: =DATE($E6, MONTH($F$3), DAY($F$3))
H6 and down: =DATE($E6, MONTH($F$4), DAY($F$4))

Thanks for the help.

This could work, however the FY starts on July 1st, and ends on June 30th. As such, the year changes half way through the year.

I think adding an "IF" in there for the month could alleviate this. I'll play around and see what I can come up with.

Thanks for the help so far!

EDIT: Here is what I figured out, which now works properly. Going off the little chart you posted above, here are the formulas
G6 and down: =DATE($E6-1,MONTH($F$3),DAY($F$3))
H6 and down: =IF((MONTH($F$4)<=6),DATE($E6,MONTH($F$4),DAY($F$4)),DATE($E6-1,MONTH($F$4),DAY($F$4)))

Now that my issue is functionaly resolved, I'm off to see if I can consolidate the formulas you posted earlier. I'll mark the title as solved, but post an update for search purposes.