+ Reply to Thread
Results 1 to 6 of 6

what can I use to count multiple items in multiple categories

  1. #1
    Forum Contributor
    Join Date
    02-28-2013
    Location
    NY, USA
    MS-Off Ver
    Excel 2019
    Posts
    101

    what can I use to count multiple items in multiple categories

    I have a long list of >1000 individuals with their travel history to multiple countries and would like to tally for each country how many of them have visited it.

    See below or the attachment for a simplified example. I tried to pivot or to use "countifs", but neither seems to be able to count. Any suggestion ?

    ID country
    Jane France
    Jane Japan
    Jane US
    Pete Brazil
    Pete Egypt
    Pete Japan
    Pete UK
    Pete US
    Tom Brazil
    Tom France
    Tom UK
    Tom US
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: what can I use to count multiple items in multiple categories

    attached file contains both a pivot table and a countif formula solution
    Attached Files Attached Files
    Last edited by alansidman; 12-06-2018 at 05:09 AM.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: what can I use to count multiple items in multiple categories

    A Pivot Table is the best way to go. See attached.

    For a production version I suggest you use a dynamic range name that automatically adjusts to the length of your data and use the name as the source for the PT
    Attached Files Attached Files

  4. #4
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: what can I use to count multiple items in multiple categories

    Quote Originally Posted by alansidman View Post
    attached file contains both a pivot table and a countif formula solution
    Hi Alan
    Your attach is for other problem.

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,755

    Re: what can I use to count multiple items in multiple categories

    Jose
    Thanks. I have updated correct file.

  6. #6
    Forum Contributor
    Join Date
    02-28-2013
    Location
    NY, USA
    MS-Off Ver
    Excel 2019
    Posts
    101

    Re: what can I use to count multiple items in multiple categories

    Thanks all for the help ! I realized that I failed to see Move to Values in pivotTable field dropdown and thus was struggling to see how pivottable can "count" the items.

+ 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 of Multiple sub categories
    By krajivgandhi in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 05-15-2017, 04:09 AM
  2. [SOLVED] Need to count items in column that match multiple data items
    By bclucas55 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-27-2014, 10:03 AM
  3. [SOLVED] Count several items in multiple columns
    By zhead in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-25-2014, 10:14 PM
  4. Collate a list of unique items only from multiple sheets into specific categories
    By Boyler_Room in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2013, 08:05 PM
  5. Replies: 3
    Last Post: 01-21-2010, 06:58 AM
  6. How to count multiple Items at once
    By mazen in forum Excel General
    Replies: 2
    Last Post: 09-11-2008, 09:00 AM
  7. Count items in multiple columns
    By Kevin98011 in forum Excel General
    Replies: 1
    Last Post: 08-11-2005, 10:05 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