I need a formula to count the number of records meeting a criteria in a
pivot table. Since the column in the pivot table that contains the
data to look at may move when the table is refreshed, I'm a little
stumped. Here's a simple example. Assume we have records of the
number of times we've contact customers by type such as in the
following.

COL A COL B COL C COL D COL E
ROW 1 Count of Contact Type
ROW 2 Customer Phone Mail Fax eMail
ROW 3 John 3 4 1
ROW 4 Pete 2 1 1
ROW 5 Sally 3 3 1 2
ROW 6 Grand Total 8 7 3 3

What I need is something that would tell me there are 2 customers that
received Mail. The formula GETPIVOTDATA("Contact", A1, "Product",
"Mail") would give me 7 no matter where that column was in the table or
how many rows were in it. How can I return the number of rows that
have data for that criteria?

Thanks
- John

A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivot table.

For example, to count unique customers per contact type, where customer
is in column A, and contact is in column B:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=S2))1,0,1)

Copy this formula down to all rows in the list.


waxwing wrote:
I need a formula to count the number of records meeting a criteria in a
pivot table. Since the column in the pivot table that contains the
data to look at may move when the table is refreshed, I'm a little
stumped. Here's a simple example. Assume we have records of the
number of times we've contact customers by type such as in the
following.

COL A COL B COL C COL D COL E
ROW 1 Count of Contact Type
ROW 2 Customer Phone Mail Fax eMail
ROW 3 John 3 4 1
ROW 4 Pete 2 1 1
ROW 5 Sally 3 3 1 2
ROW 6 Grand Total 8 7 3 3

What I need is something that would tell me there are 2 customers that
received Mail. The formula GETPIVOTDATA("Contact", A1, "Product",
"Mail") would give me 7 no matter where that column was in the table or
how many rows were in it. How can I return the number of rows that
have data for that criteria?

Thanks
- John



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks, Debra. I thought of this method but it isn't what I'm looking
for because the column to use in the calculation moves around. There
are about 10 contact types but depending on the specifc data set and
page field choices less can be showing so the column with the type I
need to use in the calculation moves around.

Could I use a multistep calculation such as? First determine the
address of the cell in row 2 that contains mail, then use the column
from that address in a count formula to count all numbers in the column
minus 1 to account for the grand total. I think this would work but
I'm not sure what functions to use to insert the address into the count
formula.

Thanks - John

Let's say your pivot table is now in A3:K28. A3 contains the button for the
page fields; the number of rows is variable. The row containing the column
headers is row 7

Define a name, say PTable1, to refer to the pivot table area. The upper left
cell in the named range should be the left-most cell in the row containing the
column headers, i.e. A7 in this example. To handle expansion of the table from
its current size, you should make the name refer to more rows than are now in
the table, so PTable1 could refer to A7:K100. (You could also make it refer to
more columns than you have now, if that could increase in the future.)

Then you can write a formula like

=COUNT(INDEX(PTable1,0,MATCH("Mail",INDEX(PTable1, 1,0),0)))-1

Look at Help for the INDEX function to understand what the "row 0" and "column
0" mean.


On 21 Feb 2005 04:44:53 -0800, "waxwing" wrote:

Thanks, Debra. I thought of this method but it isn't what I'm looking
for because the column to use in the calculation moves around. There
are about 10 contact types but depending on the specifc data set and
page field choices less can be showing so the column with the type I
need to use in the calculation moves around.

Could I use a multistep calculation such as? First determine the
address of the cell in row 2 that contains mail, then use the column
from that address in a count formula to count all numbers in the column
minus 1 to account for the grand total. I think this would work but
I'm not sure what functions to use to insert the address into the count
formula.

Thanks - John

Thanks! Works great.

- John

Thanks for the feedback.

On 21 Feb 2005 11:49:44 -0800, "waxwing" wrote:

Thanks! Works great.

- John