Sum the values in a column based on the Date ranges

**gl.shruthi** · 09-04-2012, 04:50 PM

Hi,

I have a project where in I have to provide the data by date ranges . The date ranges would be Jan 2011- Jul 2011, Aug 2011 - Dec 2011, YTD. My Input file has the data from Jan 2011 to Jul 2012. The Date field in my file reprsents each month from Jan 2011 to July 2012. I need to sum the CV by Col A, Col B and also in three different date ranges (Please refer the output tab in attached excel) .

I tried Pivoting the data and using Date filter but could not get the intended results .

Any help would be highly appreciated. Many Thanks

**jeffreybrown** · 09-04-2012, 05:35 PM

Not 100% sure you can get this done with a pivot table as your dates are not equal parts, but how about this.

I used some helper cells above which you can easily hide. May be a fancier way to get all of this into one formula without helper cells, but this is my effort.

**WorldBridge** · 09-04-2012, 05:35 PM

I fideled with this for a while.
the best solution I found was to make three pivot tables for the three periods and filter by the dates you wanted.

Example.xlsx

It got the job done but I'm not sure how useful it will be for you...

**Leizure** · 09-04-2012, 05:49 PM

Hi, I think Jeff's solution is good, that's the way I would do it. You could easily tidy up the helper cells to become part of the header row.

**WorldBridge** · 09-04-2012, 05:53 PM

I agree, his way is more elegeant than mine.
It all depends on what you need to do though.

Originally Posted by Leizure

Hi, I think Jeff's solution is good, that's the way I would do it. You could easily tidy up the helper cells to become part of the header row.

**gl.shruthi** · 09-04-2012, 06:02 PM

Thanks Jeff and worldbridge...

Jeff's solution worked for my project.. I made the helper cells as a part of the header...

Thanks once again.

**jeffreybrown** · 09-04-2012, 06:04 PM

You are very welcome and we always appreciate the feedback

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save