Counting in Pivot Tables w/o "0"

  • Thread starter Thread starter bec
  • Start date Start date
B

bec

I'm trying to add a formula in a pivot table, yet do not want to count "0".
Please help.
bec
 
What version of Excel are you using?
What is in the cells - text, numbers, dates?
What is the formula you are using?
 
Hi Bec,

Using the following data for a pivot table:

Room Color
Bathroom blue
Playroom red
Patio green
Den 0
Kitchen yellow

Drag the Room field into the Values area of the pivot table, then drag the
Color field into the Row Labels area of the pivot table.

You should end up with the following:

Row Labels Count of Room
0 1
blue 1
green 1
red 1
yellow 1
Grand Total 5


Method 1

Select the cell with '0', then from the PivotTable Tools/Options/Tools click
on Formulas, then select Calculated Item.

Double click Color from the Fields list, then double click blue from the
Items list - to create the following formula:

= Color blue+ Color green+ Color red+ Color yellow

include all fields to be counted (not able to exclude the one field that
isn't counted)

Result: Formula1 4


Method 2

Filter "0" out.

Drag the field into the Report Filter area of the pivot table, then select
multiple items, then deselect 0. This can be a problem is you are going to
use this pivot table with new data - so check this filter.

Method 3

Add a column to the source data and include the column in the pivot data
source range, with the following formula:

=if(cell=0,0,1)

Sum this column in your pivot table to get a count.

Hope this helps.

Peggy
 
Back
Top