Formula or subtotal help needed for a specific date range

By Netter3 ·
I do many spreadsheets where I have to separate then subtotal a date range.

For example: For the one I'm currently working on, the date range is 7/1 to 6/30 for every year on the spreadsheet. Of course not all years have data that ends right at 6/30, some might end earlier in the month of June (the end of the date range) as these are accident dates for insurance claims, some might have one claim for the entire date range, but I still need to separate and subtotal the date range for that particular year, whether it's one claim or many.

The Subtotal Function doesn't seem to be of any help and I can't for the life of me figure out a formula to enter a subtotal line at every instance of "the last date available in the date range" and subtotal the payment amounts.

Here's an example of what I need:

WARNER DIANE 10/16/1986 $8,481.98
<b>Date Range 7/1/86 - 6/30/87 $8,481.98</b>
BIRK JANET 2/19/1988 $6,450.00
<b>Date Range 7/1/87 - 6/30/88 $6,450.00</b>
SMITH JOYCE 10/26/1988 $8,508.80
SUMNER STEVEN 1/3/1989 $67,692.55
CRAWFORD CHANCIEJR 4/28/1989 $8**.76
SMITH MARLENA 6/14/1989 $2,155.54
<b>Date Range 7/1/88 - 6/30/89 $79,248.65</b>

The first two have only one claimant for the entire date range for that year and the last one has four claimants for the entire date range for that year. None of the accident dates end on June 30 which is the end of the date range, but earlier, or not in June at all.

So how do I tell my spreadsheet I want to subtotal by the date range of 7/1 to 6/30 of each year?

Hopefully this makes sense and you know what I'm trying to say.

By the way, I have Excel Version 2002... Ancient, I know.... : )

Thanks in advance for your help!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Related Discussions

Related Forums