More Assistance Needed with Pivot Tables

M

Michelle

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.
 
D

Debra Dalgleish

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top