I have a complicated problem. I have a spread sheet that calculates employee
points. The spreadsheet itself contains all the employee points ever
earned. I need a way to only calculate the last six months. And then every
time the months moves forward (from May to June) it will automatically
calculate the last six months including June (ex. June back to January). The
other points that were incluided from December on the spreadsheet need to
stay there just not be calculated.

Is there a way to do this and if there is how.

Thank you

--
Catherine Foster
MBS-Aumsville
Toll Free 800-682-1422
Phone 503-749-4949
Fax 503-749-4950
www.mbs-modular.com

Hi!

Try something like this:

I assume you want to sum the points (you didn't say). You also didn't say
what you want if there are not 6 months worth of points. So, I'm assuming a
lot here!

The points are in column A starting in cell A1 and there are no empty cells
within the range.

=IF(COUNT(A:A)<7,SUM(A:A),SUM(OFFSET(A1,COUNT(A:A)-1,,-6)))

This will sum all entries until there are at least 7 then it will sum the
last 6.

If this isn't what you had in mind post back and be more specific. The more
specific details you include the better the solution.

Biff

"Cat Foster" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a complicated problem. I have a spread sheet that calculates
>employee points. The spreadsheet itself contains all the employee points
>ever earned. I need a way to only calculate the last six months. And then
>every time the months moves forward (from May to June) it will
>automatically calculate the last six months including June (ex. June back
>to January). The other points that were incluided from December on the
>spreadsheet need to stay there just not be calculated.
>
> Is there a way to do this and if there is how.
>
> Thank you
>
> --
> Catherine Foster
> MBS-Aumsville
> Toll Free 800-682-1422
> Phone 503-749-4949
> Fax 503-749-4950
> www.mbs-modular.com
>

Catherine,

It's a bit difficult to give a complete answer without knowing how your data
is laid out but as an illustration, with dates in column A, Pinits in column
B and the end date of the current month in G2 generated by the formulas:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

and the starting date for six months ago in G3 generated by the formula:

=DATE(YEAR(TODAY()),MONTH(TODAY())-5,0)

the points for the last six months will then be returned by:

=SUMPRODUCT((A2:A20<G2)*(A2:A20>G3)*(B2:B20))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


"Cat Foster" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a complicated problem. I have a spread sheet that calculates
>employee points. The spreadsheet itself contains all the employee points
>ever earned. I need a way to only calculate the last six months. And then
>every time the months moves forward (from May to June) it will
>automatically calculate the last six months including June (ex. June back
>to January). The other points that were incluided from December on the
>spreadsheet need to stay there just not be calculated.
>
> Is there a way to do this and if there is how.
>
> Thank you
>
> --
> Catherine Foster
> MBS-Aumsville
> Toll Free 800-682-1422
> Phone 503-749-4949
> Fax 503-749-4950
> www.mbs-modular.com
>