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.

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.

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

Can you show a couple of sample records from the source data, and what
you'd like to display in the pivot table?

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

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

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.

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

Awesome Debra! I guess I am confused on the logic, but will try to read up
on it!
You are a lifesaver and a huge timesaver!

"Debra Dalgleish" wrote:

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