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

Hi Michael,

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

Then build a pivot table with Sumof One Rank or More

Ed Ferrero
www.edferrero.com

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

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.
On the field button for Number of Rankings, click the dropdown arrow
Remove the check mark for zero, and click OK

Michael C wrote:
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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html