Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
I cannot figure out why in my pivot table, it sums all the appropriate data,
but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
If the field that contains blanks is in the Row area, add another field,
that contains data in every record, to the Data area. For that field, change the Summary function to Count, and it will show a count of the blank items. ben****er2 wrote: I cannot figure out why in my pivot table, it sums all the appropriate data, but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
Debra -
thanks, but that doesn't seem to help either. The two fields that i am using in the pivot table are full and the summary function is set to "Count", but it is still not counting the blanks. The field that contains the blanks is in a column...does that matter. "Debra Dalgleish" wrote: If the field that contains blanks is in the Row area, add another field, that contains data in every record, to the Data area. For that field, change the Summary function to Count, and it will show a count of the blank items. ben****er2 wrote: I cannot figure out why in my pivot table, it sums all the appropriate data, but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
Can you show a couple of sample records from the source data, and what
you'd like to display in the pivot table? ben****er2 wrote: Debra - thanks, but that doesn't seem to help either. The two fields that i am using in the pivot table are full and the summary function is set to "Count", but it is still not counting the blanks. The field that contains the blanks is in a column...does that matter. "Debra Dalgleish" wrote: If the field that contains blanks is in the Row area, add another field, that contains data in every record, to the Data area. For that field, change the Summary function to Count, and it will show a count of the blank items. ben****er2 wrote: I cannot figure out why in my pivot table, it sums all the appropriate data, but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
The actual spreadsheet is to large, but here is the basics
Region Company Name Acct. Designation Outage Date CA Norske Canada P1 (dropdown) CA Petro Canada P1 (dropdown) Jun-06 (dropdown) W Cal Energy P2 (dropdown) Oct-06 (dropdown) W Montenay P2(dropdown) Nov-06(dropdown) SW Koch P1(dropdown) Jan-07 (dropdown) what I would like to see in the pivot is this: COUNT OF OUTAGE DATE REGION ACCT DESIGNATION OUTAGE DATE CA W SE P1 Jun-06 1 Jul - 06 Aug-06 | | | (blanks) 21 12 1 P2 (same thing again) It is filling in the appropriate counts under each region and outage date, but the blanks are not filling in at all. When I double click (in the example above) in the empty cell where the 21 should be, I get another spreadsheet showing the detail of the 21 accounts missing outage dates. That is really the only reason I want this pivot table to see where we are lacking outage dates for our clients. Thanks again for your continued help. "Debra Dalgleish" wrote: Can you show a couple of sample records from the source data, and what you'd like to display in the pivot table? ben****er2 wrote: Debra - thanks, but that doesn't seem to help either. The two fields that i am using in the pivot table are full and the summary function is set to "Count", but it is still not counting the blanks. The field that contains the blanks is in a column...does that matter. "Debra Dalgleish" wrote: If the field that contains blanks is in the Row area, add another field, that contains data in every record, to the Data area. For that field, change the Summary function to Count, and it will show a count of the blank items. ben****er2 wrote: I cannot figure out why in my pivot table, it sums all the appropriate data, but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Tables and blanks
In your example, you're using outage date as the Data field.
Remove it, and put another copy of the Company Name field in the data area. It should appear as Count of Company Name, and will give the count for each Outage Date, including blanks. ben****er2 wrote: The actual spreadsheet is to large, but here is the basics Region Company Name Acct. Designation Outage Date CA Norske Canada P1 (dropdown) CA Petro Canada P1 (dropdown) Jun-06 (dropdown) W Cal Energy P2 (dropdown) Oct-06 (dropdown) W Montenay P2(dropdown) Nov-06(dropdown) SW Koch P1(dropdown) Jan-07 (dropdown) what I would like to see in the pivot is this: COUNT OF OUTAGE DATE REGION ACCT DESIGNATION OUTAGE DATE CA W SE P1 Jun-06 1 Jul - 06 Aug-06 | | | (blanks) 21 12 1 P2 (same thing again) It is filling in the appropriate counts under each region and outage date, but the blanks are not filling in at all. When I double click (in the example above) in the empty cell where the 21 should be, I get another spreadsheet showing the detail of the 21 accounts missing outage dates. That is really the only reason I want this pivot table to see where we are lacking outage dates for our clients. Thanks again for your continued help. "Debra Dalgleish" wrote: Can you show a couple of sample records from the source data, and what you'd like to display in the pivot table? ben****er2 wrote: Debra - thanks, but that doesn't seem to help either. The two fields that i am using in the pivot table are full and the summary function is set to "Count", but it is still not counting the blanks. The field that contains the blanks is in a column...does that matter. "Debra Dalgleish" wrote: If the field that contains blanks is in the Row area, add another field, that contains data in every record, to the Data area. For that field, change the Summary function to Count, and it will show a count of the blank items. ben****er2 wrote: I cannot figure out why in my pivot table, it sums all the appropriate data, but does not return a count of "blanks". This is a critical piece of managing the data. Any suggestions. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum vs. Count in Pivot Tables | Excel Discussion (Misc queries) | |||
Add quartiles and mode functions to pivots tables in Excel! | Excel Discussion (Misc queries) | |||
How do I stop my Pivot tables from spelling out (blank)? | Excel Discussion (Misc queries) | |||
HOW TO MERGE TWO TABLES | Excel Worksheet Functions | |||
Hiding rows containing zeroes or blanks in pivot tables? | Excel Discussion (Misc queries) |