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. |
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 |
All times are GMT +1. The time now is 04:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com