Pivot Table question or a simpler suggestion

[Michael C]

Hello,

I have a group of data. Among the data fields are the following:

Name Group/Association Number of rankings

The number of rankings are either 0, 1, or 2. I would like to be able
to know for a given group, how many members of the group have at least
1 ranking.

My first thought was a Pivot Table but if I use the count field
setting, it counts all populated cells. If I leave the cell blank,
that works instead of using a zero but I would prefer not to leave it
blank as it may cause confusion to others I pass the sheet on to.

I thought about writing something in VBA but this seems a little
advanced for my vba skills.

Is there a way to count only non zero values with a pivot table? Is
there a simpler solution that I may be overlooking?

Thanks.

-Mike

 

[Ed Ferrero]

Hi Michael,

Add a helper column,
heading 'One Rank or More'
formula =IF(C2>0,1,0)

Then build a pivot table with Sumof One Rank or More

Ed Ferrero
www.edferrero.com

 

[Debra Dalgleish]

In the pivot table, add Group to the Row area, add Number of Rankings to
the Column area, and add Name to the Data area, as Count of Name.