Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table: How to count the same variables in different columns

    Hi I am trying to figure out how to count the number of times a single variable is displayed in the example below

    ID# Category1 Category2 Category3
    1 X Y Z
    2 Z X Y
    3 X Z Y

    I want the output to be
    Var Count
    X: 3
    Y: 3
    Z: 3


    I know i can use the countif function but its a lot of data (~ 20vars by 20 vars) and I would prefer to not use that method and have to add 40 more columns.

    Thanks!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Need Help,

    Are you counting by columns? You can count the entire range

    Code:
    =COUNTIF(B2:D4,"X")
    HTH,
    Maud

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI< you would need to normalise your data to use a pivot table. (though you can use a pivot table to do that quickly)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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