Thread: Sum months worth of purchases from pivot table

Hi Guys,

I have two pivot tables set-up for two different sections to show me their purchases. I was trying to do a table that sums all the purchases for each month were dates are in format: 09/01/2011 in a pivot table and in the table that I want the calculation it is: 01/2011.

In the first pivot table which is located in AT3 I have it this way:

From AT5 going down are dates: In AU4 Is a letter "M" which represents Section under which I have Prices:
09/01/2011 125.5
22/01/2011 23.45

My table is located in BB4 with dates going down as from BB5 and Column "Price" in BC4 with formulas starting from BC5

01/2011
02/2011
03/2011
etc.etc.

I vave tried the following formulas:

=SUM(INDEX($AU$5:$AU$2143,MATCH($BB6,RIGHT($AT$5:$AT$2143,7),0),MATCH("M",$AU$4,0)):INDEX($AU$5:$AU$2143,MATCH($BB6,RIGHT($AT$5:$AT$2143,7),0),MATCH("M",$AU$4,0)))

and

=SUM(INDEX($AU$5:$AU$2143,MATCH(01/01/2011,$AT$5:$AT$2143,0),MATCH("M",$AU$4,0)):INDEX($AU$5:$AU$2143,MATCH(31/01/2011,$AT$5:$AT$2143,0),MATCH("M",$AU$4,0)))

But neither of them works.
I am cracking my head over this and I don't know how to change it to make it work.

Would anybody know what I do wrong?

Any help appreciated.

Thanks for reading this.
Simon

Couldn't you use your pivot tables as source for another PT where you group dates by month ?

Maybe posting a sample would help ( if possible)

I guess I could, but I don't know how to tell another pivot table to show months worth.

It will be a thing to uncheck all unwanted dates which will be growing with time.

Correct me if I am wrong.

Simon

If you have dates as row fields, right click, select " group and show detail" slect group and select "month"

You cannot have blanks between dates and they must be real dates, not text looking like dates

When I had only 2 Janury dates it was OK.

But then I added February and it came back with parent tables results which were plane dates and cannot group now.

I attach that spreadsheet if you could have a look as I am a bit lost now. Don't know which direction I shall be heading with it.

Thanks for your effort.
Simon

Would you know guys if there is a way to manipulate a sum formula to sum all values in one column if in the corresponding row of another column a value true for criteria >=01/01/2011 and =<31/01/2011?

I was trying to do it with sum if but I couldn't connect them two criterias together.

If anyone knows it would be great if can share that knowledge.

Many thanks

I have found a solution to that.
It requires manual amendmends but works fine.

=SUM(SUMIF($AT$5:$AT$2300,">=01/01/2011",$AU$5:$AU$2300)-SUMIF($AT$5:$AT$2300,">31/01/2011",$AU$5:$AU$2300))

Thanks for giving it a try.
Simon

Originally Posted by Ramzes

When I had only 2 Janury dates it was OK.

But then I added February and it came back with parent tables results which were plane dates and cannot group now.

I attach that spreadsheet if you could have a look as I am a bit lost now. Don't know which direction I shall be heading with it.

Thanks for your effort.
Simon

Hi Simon,
sorry that I couldn't follow up

Your problem for grouping is due to the fact you have blank lines in your source data for dates.
To avoid this you could use dynamic ranges ( see this link for an example)