Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
Hi Jo,
I would suggest you do this outside a pivot table using formulas. Inside a pivot table requires ALL kind of gyrations. -- Thanks, Shane Devenshire "Jo" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
That is what I thought but the pivot table has been helpful for the database
created. Thanks "ShaneDevenshire" wrote: Hi Jo, I would suggest you do this outside a pivot table using formulas. Inside a pivot table requires ALL kind of gyrations. -- Thanks, Shane Devenshire "Jo" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
Hi Herbert,
But the goal is to get it into 1 (one) pivot table, not two. -- Thanks, Shane Devenshire "Herbert Seidenberg" wrote: Use Excel 2007 PivotTable. No gyrations, no formulas: http://www.savefile.com/files/1809863 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
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. -- Thanks, Shane Devenshire "Jo" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
You are correct. I only want to see the current and the MTD #s.
"ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Jo" wrote: 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table--Daily and MTD Total
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. -- Thanks, Shane Devenshire "Jo" wrote: You are correct. I only want to see the current and the MTD #s. "ShaneDevenshire" wrote: 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. -- Thanks, Shane Devenshire "Jo" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Row Total | Excel Discussion (Misc queries) | |||
Showing Pivot Table total on Associated Pivot Chart | Excel Discussion (Misc queries) | |||
Linking Daily Worksheet To Daily Invoice Total | Excel Worksheet Functions | |||
Sub Total In Pivot Table | Excel Discussion (Misc queries) |