Pivot Table--Daily and MTD Total

J

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?
 
S

ShaneDevenshire

Hi Jo,

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

Jo

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

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.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top