Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 73
Default Count only if another column has data in Pivot Table?

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?!!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,979
Default Count only if another column has data in Pivot Table?

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.


wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
PIVOT TABLE COLUMN Wes Excel Discussion (Misc queries) 3 August 18th 06 11:08 PM
Pivot Table Data - Troubleshoot needed aly1cat Excel Discussion (Misc queries) 4 August 4th 06 10:18 PM
pivot table rmsterling Excel Discussion (Misc queries) 5 November 14th 05 05:40 PM
Pivot table, dynamic data formula Excel GuRu Excel Discussion (Misc queries) 3 May 3rd 05 10:45 PM
How can deleted data reappear in a refreshed pivot table in Excel excel_user123456 Excel Discussion (Misc queries) 3 February 23rd 05 09:34 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"