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,789
    Thanks
    139
    Thanked 705 Times in 639 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,313
    Thanks
    3
    Thanked 211 Times in 194 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
  •