Grouping data
 

Hi, I have a worksheet where data is imported from an Access Database.

Column A contains the month and year on mmm-yy format and is in descending
order i.e. latest month is always at the top.

Column B contains a number

There is about 10 years worth of data.

I need to arrive at a way where I see can instantly the sum of column B for
any year's data. Added to the problem that it has to match the Academic
Year which for us starts in August and ends the following July

Is this possible? And if so can someone point me in the right direct on
going about this.

Many thanks

A

Grouping data
 

Alex Hammerstein;281215 Wrote:
Hi, I have a worksheet where data is imported from an Access Database.

Column A contains the month and year on mmm-yy format and is in
descending
order i.e. latest month is always at the top.

Column B contains a number

There is about 10 years worth of data.

I need to arrive at a way where I see can instantly the sum of column B
for
any year's data. Added to the problem that it has to match the
Academic
Year which for us starts in August and ends the following July

Is this possible? And if so can someone point me in the right direct
on
going about this.

Many thanks

A

Hi,
you can use a Pivot Table and group the dates by year.
You van set beginning and end of year as you wish

HTH


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=78534

Grouping data
 

Hello Alex,

Your date format is a little bit unfortunate. I would recommend to use
my UDF Sfreq (see www.sulprobil.com) if your MMM-YY would be easier to
use.

Regards,
Bernd

Grouping data
 

Excel 2003

presume your start date is in C1, end date in D1

in C2 insert the formula:

=SUMPRODUCT((A1:A65536=C1)*(A1:A65536<=D1),--B1:B65536)



or this array formula (to be inserted with CTRL+SHIFT+ENTER)

=SUM(IF((A1:A65536=C1)*(A1:A65536<=D1),B1:B65536, ))


for Excel 2007 you may increase the range over 65536 rows

HTH


On 24 Mar, 15:40, Alex Hammerstein wrote:
Hi, *I have a worksheet where data is imported from an Access Database.

Column A contains the month and year on mmm-yy format and is in descending
order i.e. latest month is always at the top.

Column B contains a number

There is about 10 years worth of data.

I need to arrive at a way where I see can instantly the sum of column B for
any year's data. * Added to the problem that it has to match the Academic
Year which for us starts in August and ends the following July

Is this possible? And if so can someone point me in the right direct on
going about this.

Many thanks

A

Grouping data
 

I have had the saem issue with Fiscal year calcs

Use this formula to convert the dates to Academic Year (put them in another
column like C)

=if(month(A1)<8,year(A1),year(A1)+1)

Then use a sum if formula

=SUMIF(C2:C500,2008,B2:B500)

This will add values in column B where Column C=2008

"Alex Hammerstein" wrote:

Hi, I have a worksheet where data is imported from an Access Database.

Column A contains the month and year on mmm-yy format and is in descending
order i.e. latest month is always at the top.

Column B contains a number

There is about 10 years worth of data.

I need to arrive at a way where I see can instantly the sum of column B for
any year's data. Added to the problem that it has to match the Academic
Year which for us starts in August and ends the following July

Is this possible? And if so can someone point me in the right direct on
going about this.

Many thanks

A