Grouping data

[Alex Hammerstein]

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

 

[Pecoflyer]

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 i
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
for
any year's data. Added to the problem that it has to match th
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 direc
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

HT

--
Pecoflye

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

*Adding your XL version* to your post helps finding solution faste

 

[Bernd P]

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

 

[Jarek Kujawa]

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

 

[Paul C]

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

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Accommodates any Fiscal, Academic Year.
http://www.mediafire.com/file/m3tzinkuyyz/04_03_09a.xlsx

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Accommodates any Fiscal, Academic Year.
http://www.mediafire.com/file/m3tzinkuyyz/04_03_09a.xlsx

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Accommodates any Fiscal, Academic Year.
http://www.mediafire.com/file/m3tzinkuyyz/04_03_09a.xlsx