[Highland Learner]

Hi, I'm a beginner when it comes to formula for selecting date ranges! I have tried =SUMIF combos but and hitting a wall. Any help would be very welcome. Pretty basic the request.

2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

Just tired this =SUM(IF(D,">1995",E:E)(D,"<1990",E:E)) but did not work.

[I do have a slightly more complicated request of using real dates and being able on select all for the last 15 years for example and 15 to 20 years, but I think we should leave that for another day}

Any help very welcome, thanks.

[Aj_old]

Hi and wellcome to TSG forum!
Try use this:

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18))

where: A7:A18 - contains years, B7:B18 - contains the data to sum, A6 is the star year of the period, and B6 is the end year of the period .

[OBP]

AJ, brilliant

Of course I would use Access

[Aj_old]

There's another way to do this, Using Autofilter, and Subtotal function, I use them a lot!

[Highland Learner]

That did the tick! Many thanks for your time... (Better than the rubbish book I brought!)

Regards, JB

[Aj_old]

You are wellcome!
Now you can mark the thread as solved, if it's all of course

[Highland Learner]

I had the auto filters on but I want to produce 3 or 4 different date ranges, then graph the data and be able to add in new rows.

I will look to access but I'm in a new job and getting things up and running, you would not believe how it was being done, a different sheet for each year range, ouch!

[Highland Learner]

will do

[Highland Learner]

Not sure if your still there but I tried to use pivot tables to do this, and it seemed a total pain. Are they any good? (For this sort of sorting of data)? thanks

[Aj_old]

Originally Posted by Highland Learner

Not sure if your still there but I tried to use pivot tables to do this, and it seemed a total pain. Are they any good? (For this sort of sorting of data)? thanks

Pivot tables are very fine, but they wont let you use the periods (for example 1990-1995), but will do it for every year in the list!

[Highland Learner]

ah thanks, that's no good for me! great so at least I can tell the boss they don't work for this specifc issue.

Sorry there was one more, I need a counter of exactly the same as before, so just need a cell to say '18' if there are 18 rows within 1990 and 1995.

[Aj_old]

Originally Posted by Highland Learner

ah thanks, that's no good for me! great so at least I can tell the boss they don't work for this specifc issue.

If you wanna you can add one more column in which you'll have the years grouped by periods, and after that use pivot table

Originally Posted by Highland Learner

Sorry there was one more, I need a counter of exactly the same as before, so just need a cell to say '18' if there are 18 rows within 1990 and 1995.

U can use this formula:

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6))

[Highland Learner]

thanks AJ

[Aj_old]

You are wellcome,

[Highland Learner]

Hi,

OK I thought this was going to work fine but there is a logic problem I've come across.

I have a list of 160 rows with the year being the key data field and selecting various ranges say 1995 to 2006.

I need to capture the data in a way that I need to keep historic info for years gone (2006,2007) but then alter the sheet (remove a row if no longer relevant) for current and future years.

I was thinking of having a final column with a "year removed field" and the main formula above being dependant on that for inclusion, make sense?

Many thanks