Pivot Table--Daily and MTD Total

[Jo]

I would like my pivot table report to have only daily information and the MTD
information.

Example
Customer 09/23 MTD
A 100 2000
B 200 5000
Total 300 7000

Does anyone know how to accomplish this?

 

[ShaneDevenshire]

Hi Jo,

I would suggest you do this outside a pivot table using formulas. Inside a
pivot table requires ALL kind of gyrations.

 

[Jo]

That is what I thought but the pivot table has been helpful for the database
created.
Thanks

 

[Herbert Seidenberg]

Use Excel 2007 PivotTable.
No gyrations, no formulas:
http://www.savefile.com/files/1809863

 

[ShaneDevenshire]

Hi Herbert,

But the goal is to get it into 1 (one) pivot table, not two.

 

[ShaneDevenshire]

Hi,

After Herbert's response I went back and looked at your original request,
and it generated a question - do you only want to see the daily information
for a single day or for all the days in the month? Because if you wanted to
see if for all days as well as the MTD then it would be just a regular pivot
table. But I assume you want to see the current day's numbers and the MTD
numbers only.

 

[Jo]

You are correct. I only want to see the current and the MTD #s.

 

[ShaneDevenshire]

Hi,

Well, then I go back to my original response, which was to do it outside the
pivot table with formulas:

I don't know what your original data looks like or if this is any use, but
suppose the data is in the range A1:B100 with dates in column A and amts in B
and titles on row 1.

Then enter the date you want to calculate for in cell E2, for example, and
in F2 the formula
=INDEX(B2:B100,MATCH(E2,A2:A100,0))
This formula returns the amount for the date in E2.
In G2 enter the formula
=SUMPRODUCT(--(MONTH(E2)=MONTH(A2:A100)),--(A2:A100<=E2),B2:B100)
This formula returns the MTD figure
If you want YTD
=SUM(OFFSET(B2,0,0,MATCH(E2,A2:A100,0)))


If any of these help please click the Yes button.