• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Counts from a Pivot Table

J

JuliusV

Member
Hi,

I have attached mock data of what I am trying to receive the counts for.

Essentially, I have a pivot table with Employee Names and the counts of likes/dislikes. From there, I want to create a table that sums the dislikes based on the Managers of the employee names. I am not allowed to use the source data, but have the relationship between the employee and the manager.

As a note there are three tabs:

Summary Tab - Here is where I am trying to create the manager table
LikeDislike Data - Shows if the employee likes or dislikes
Manager Employee Relationship - Shows who the employees manager is

Thank you for the help!
 

Attachments

  • Chandoo Counts Questions_v1.xlsx
    15.9 KB · Views: 1
Hi Julius,

I think we can get there fairly easy by changing PT layout. First, insert a column between C and D to make some space. Then, on the PT Design Ribbon, Change Report Layout -Tabular, Remove all Subtotals and Grand Total. Filter the Like/Dislike field to only show Dislike. Final result will look like:
upload_2015-6-30_14-56-58.png

Then, your formula in H4 is:
=SUMIF($D$3:$D$14,G4,$C$3:$C$14)
 

Attachments

  • Chandoo Counts Questions_LM.xlsx
    15.3 KB · Views: 1
Hi Luke M,

Thank you very much for your help and for understanding my question!

You definitely made it a lot simpler than all the index match match I was thinking I needed to do.

Thanks,

JuliusV
 
Hi Julius,

If you use Power Pivot this is easy with that as their is a relationship in two tables. I have Excel 2010 add-in for it, if you are interested I can share the file with you.

Regards,
 
Hi ,

Even the pivot tables can be dispensed with , if the data on the Manager Employee Relationship tab repeats the manager's name.

Use the following array formula :

=SUM(COUNTIFS('LikeDislike Data'!$A$2:$A$54,IF('Manager Employee Relationship'!$A$2:$A$17=F4, 'Manager Employee Relationship'!$B$2:$B$17),'LikeDislike Data'!$B$2:$B$54,"Dislike"))

Enter this in G4 and copy down.

Narayan
 
Thank you for all the help. I think the pivot table method was the easiest. My original thought process over complicated the problem.
 
You must log in or register to reply here.
Back
Top