Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CODE Assistance needed PLEASE please please | Excel Discussion (Misc queries) | |||
Help needed with pivot tables | Excel Discussion (Misc queries) | |||
VBA Assistance Needed | Excel Discussion (Misc queries) | |||
Pivot Table Assistance Needed | Excel Worksheet Functions | |||
Pivot Table Assistance Needed | Excel Worksheet Functions |