+ Reply to Thread
Results 1 to 11 of 11

Counts # of Yes and No in a pivot table

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Counts # of Yes and No in a pivot table

    Hi,

    I've been looking all over for a solution, but to no avail.

    I would like to get counts of Yes or No from a table with a.o. questions. In my example there are 4 questions, however the actual data has columns and will eventually be filtered based on employee, rank, location, territory etc, so unfortunately I couldn't just refer to the table with a formula's, but that could very well be my ignorance ;-).

    In the following picture the blue stuff are my table and pivot results, yellow is what I would like to see.

    PivotExample.png

    Any help would be appreciated....

    Mike
    Attached Files Attached Files
    Last edited by blak9; 03-25-2021 at 09:14 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counts # of Yes and No in a pivot table

    Hi. I'm not very clear about what you want... but maybe:


    =COUNTIF(Tabel4[Question1],$F9)

    copied across and down.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Counts # of Yes and No in a pivot table

    Hi Glenn,

    Thanks for your reply, as a matter of fact, that was my first solution as well. And then came the ever growing need of data filters such as employee, rank, location, etc (which maybe I should've included in the data table). The list is more than 1000 rows long, so it's also hard to keep unique lists up to date of employees, locations, etc...

    Regards,
    Mike

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counts # of Yes and No in a pivot table

    In that case, I think you have oversimplified your description and sample file... Can you reconsider and repost??

  5. #5
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Counts # of Yes and No in a pivot table

    I suppose I did, please let me try again... ;-)

    PivotExample.png

    I've also included an updated version of my sheet...


    Many thanks in advance...
    Attached Files Attached Files
    Last edited by blak9; 03-25-2021 at 09:17 AM.

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Counts # of Yes and No in a pivot table

    https://exceljet.net/formula/count-v...-with-criteria

    Following the above example, if the filtering is achieved by you applying it to the data columns

    =SUMPRODUCT((E$3:E$17="n")*(SUBTOTAL(103,OFFSET(C3,ROW(C$3:C$17)-MIN(ROW(C$3:C$17)),0)))) appears to work for your data

  7. #7
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Counts # of Yes and No in a pivot table

    Hi Davsth,

    The filtering won't be applied by the actual data columns, unfortunately it has to be done by the Pivot filters... :-(

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counts # of Yes and No in a pivot table

    With slicers and a formula to count visible cells only (on another sheet to avoid layout problems)??

    =SUMPRODUCT((Tabel4[Q1]=$D18)*(SUBTOTAL(103,OFFSET('Yes vs No'!$E$3,ROW(Tabel4[Q1])-MIN(ROW(Tabel4[Q1])),0))))

    copied across and down.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Counts # of Yes and No in a pivot table

    Thanks Glenn, i'll try your solution tomorrow and let you know if that would work for me.. 👍

  10. #10
    Registered User
    Join Date
    08-19-2015
    Location
    London, England
    MS-Off Ver
    365
    Posts
    67

    Re: Counts # of Yes and No in a pivot table

    Hi Glenn, your solution works beautifully..! Again many Thanks with a capital T.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Counts # of Yes and No in a pivot table

    You're welcome & thanks for the feedback.

+ 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] Counts using pivot table
    By luckysingh in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-01-2019, 10:13 PM
  2. [SOLVED] Subtracting one sum of counts from another sum of counts in a pivot table
    By steve78 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-07-2019, 01:35 PM
  3. How to sum counts from a certain pivot table
    By Horrid Wilting in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-06-2018, 03:21 AM
  4. [SOLVED] Pivot Table Help - Counts
    By degross77 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-09-2018, 04:01 PM
  5. Please help! How to tell which counts are the same in pivot table
    By dukepoco224 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-11-2015, 01:59 PM
  6. Replies: 2
    Last Post: 05-08-2013, 04:56 PM
  7. Pivot table - SUM of COUNTS?
    By nick_danger in forum Excel General
    Replies: 1
    Last Post: 03-10-2008, 11:43 PM

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