+ Reply to Thread
Results 1 to 5 of 5

Add Grouped Count Column to Pivot

  1. #1
    Registered User
    Join Date
    10-12-2023
    Location
    United States
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20130) 64-bit
    Posts
    2

    Question Add Grouped Count Column to Pivot

    I have this pivot table:
    Capture.PNG

    In between columns B and C, I'd like to insert a column that counts Employee Names in column C grouped by column B.

    For example, in the picture above, Senior Consultant April will have the number 8 - a count of employees under her.

    Can I create this custom field in the pivot? Or will I have to engineer a formula to do so outside of the pivot table?
    1.PNG

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Add Grouped Count Column to Pivot

    IMO, it would be much easier to work a formula within the data itself.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Add Grouped Count Column to Pivot

    I agree with jeffreybrown.

    Create a new column in your data using a formula like this (assuming Employee_Region is in column A and Employees_Senior_Consultant is in column B:
    =COUNTIFS(A:A,A2,B:B,B2)
    and drag it down the column. Give it a header that makes sense to you.

    Then drag that field into the pivot table Rows between Employees_Senior_Consultant and Employee_Name.

    See attached
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-12-2023
    Location
    United States
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20130) 64-bit
    Posts
    2

    Re: Add Grouped Count Column to Pivot

    Thanks for the solution! Unfortunately, since the data is extremely granulated (by working session) there are duplicates. Even after using a formula that filters out employees based on job title, I still get an outrageously high number.

    Attachment 845815

    I put this count formula in the data:

    Attachment 845816

    Basically, if filter criteria is met, count how many times a seniors name appears. I tried to offset the duplicates by subtracting the amount of times the subordinates name appears and thought I could just add 1, but this won't create a rolling count.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Add Grouped Count Column to Pivot

    None of your attachments open for me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count percentage in Pivot table using two Count column
    By LeeWen in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-14-2022, 06:30 AM
  2. [SOLVED] Pivot Table values grouped by month do not appear in column format
    By DAVID766 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-01-2019, 01:17 PM
  3. Pivot showing percentage in Grand total grouped by first column
    By matpj in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-16-2014, 09:47 AM
  4. some date fields to be grouped in column in pivot table
    By anilrini in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-27-2012, 10:16 AM
  5. Replies: 1
    Last Post: 07-12-2012, 01:26 PM
  6. using count function with dates grouped into months
    By bonds in forum Excel General
    Replies: 4
    Last Post: 06-11-2010, 03:48 PM
  7. Replies: 1
    Last Post: 02-09-2010, 06:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1