Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 310
Default More Assistance Needed with Pivot Tables

I received repsonses to my original post, however it is still not working.
Here is the original question and my followup question

I am having a problem, I am new to the Pivot Tables in Excel. Here is my same

username field Count field
user1 12
user2 2
user3
user4 4

I have been able to create a pivot table that will show me all the users,
there count field, and then a grand total of 18 for the count field. However
what I really need to do is count the number of users that have a value in
the count field.

Does anyone have any suggestions for me? When I go into the field settings
it is performing a count but a count on the value in the count field.

Thanks for this, however I must be not understanding this correctly. I
tried to drag the Count Field back in for a second time.

Some more data:

I actually have three fields

Username
Department
Month

My table currently is this:

In the row field is is the department but a count of username
Then I have a month drop column field
In the Drop Data items sections it populates with the number 18. This is
the total count from the data below. I need the data items to display the
number of users that have a value - which is 3 in the example.

Hope this make sense, I wish I could post a screen capture of the pivot table
it has created.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default More Assistance Needed with Pivot Tables

You an add a column to the source data, and calculate the count there,
then add the new column to the pivot table. For example, with Username
in column A, and Count in column B:

Add a new column C, with the heading CountNum
IN cell C2, enter the formula:
=IF(B2="",0,IF(SUMPRODUCT(--($A$1:A2=A2),--($B$1:B2<""))=1,1,0))
Copy the formula down to the last row of data.

Refresh the pivot table, and add the CountNum column to the data area,
as Sum of CountNum

Michelle wrote:
I received repsonses to my original post, however it is still not working.
Here is the original question and my followup question

I am having a problem, I am new to the Pivot Tables in Excel. Here is my same

username field Count field
user1 12
user2 2
user3
user4 4

I have been able to create a pivot table that will show me all the users,
there count field, and then a grand total of 18 for the count field. However
what I really need to do is count the number of users that have a value in
the count field.

Does anyone have any suggestions for me? When I go into the field settings
it is performing a count but a count on the value in the count field.

Thanks for this, however I must be not understanding this correctly. I
tried to drag the Count Field back in for a second time.

Some more data:

I actually have three fields

Username
Department
Month

My table currently is this:

In the row field is is the department but a count of username
Then I have a month drop column field
In the Drop Data items sections it populates with the number 18. This is
the total count from the data below. I need the data items to display the
number of users that have a value - which is 3 in the example.

Hope this make sense, I wish I could post a screen capture of the pivot table
it has created.



--
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
CODE Assistance needed PLEASE please please N.F[_2_] Excel Discussion (Misc queries) 0 July 9th 07 08:36 PM
Help needed with pivot tables danielz Excel Discussion (Misc queries) 4 April 5th 06 10:22 AM
VBA Assistance Needed RalphB Excel Discussion (Misc queries) 5 February 22nd 06 07:16 PM
Pivot Table Assistance Needed Barb R. Excel Worksheet Functions 4 May 11th 05 05:11 PM
Pivot Table Assistance Needed Barb R. Excel Worksheet Functions 0 May 11th 05 02:51 PM


All times are GMT +1. The time now is 01:01 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"