Count only if another column has data in Pivot Table?

[robotman]

Pivot Table newbie confusion...

I have a pivot table based on this data:

Col 1 = customer type
Col 2 = number of customers per acct
Col 3-14 = hours in a particular month (jan - dec)

I'm trying to set up a pivot table that will show me how many customers
received service in a particular month. So in my pivot table I
consolidate the service hours and sum the customer type for a
particular month by choosing a particular column for the "data" section
of the table. This works fine. For example, I can just look at the
service hours in October for each customer type by choosing Col 11
(Oct) for the "data" section.

What I would like to do is know how many customers received service in
a particular month (rather than the total customers I have ever
serviced...which is what happens if I try adding Col 2 to the "data"
section). To do this, the pivot table would need to only sum the
number of customers IF there is a value in the month column I have
selected.

Hopefully that isn't too confusing...

Any ideas?!!

 

[Debra Dalgleish]

You could add more columns to your source table, and calculate the
customers per month. For example, in cell O1, enter:

=C1&"C"

and copy across to cell Z1
In cell O2, enter: =$B2*C2
Copy that formula across to Z2, and down to the last row of data.

Add the new columns to the pivot table range, and add these fields to
the pivot table data area to see the customer totals.