Results 1 to 3 of 3

Thread: Lookup and Count Using Pivot Table

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Lookup and Count Using Pivot Table

    I have 100000 lines in excel where I have data in column D and in Column H. If the data in column D matches the value in Cell A22, I want to then look at the value on the same row in column H and count how many times there is a match to that value. I want the count of the # of those matches to be output.

    Example:
    Order Country Misc Misc 1 Misc 2 Install Country
    USA Misc Misc 1 Misc 2 Spain
    USA Misc Misc 1 Misc 2 Spain
    France Misc Misc 1 Misc 2 France
    France Misc Misc 1 Misc 2 Morocco
    France Misc Misc 1 Misc 2 Morocco
    USA Misc Misc 1 Misc 2 USA
    Germany Misc Misc 1 Misc 2 USA
    Germany Misc Misc 1 Misc 2 Spain
    USA Misc Misc 1 Misc 2 Spain

    I would like to Look up order country USA and have the output say Spain = 3; USA = 1; then Lookup France and have the output say France = 1; Morocco = 2

    What is the excel formula string in combination that will allow me to do that?

    Thanks

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi RobExcel,


    Welcome to ExcelFox!!

    You can easily done this by creating a Pivot Table.

    Create a pivot table > Put Order Country in Page Field, Country in both Row field as well as Data field.

    Now select the order country , you will get the list of countries and their count.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    In case you are struggling to figure out how to do that, here's an example showing what Admin explained above.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Preparing Trial Balance Using Pivot Table
    By Zaigham in forum Excel Help
    Replies: 4
    Last Post: 05-14-2013, 11:57 AM
  2. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  3. Pivot Table - Volume by Day and Half Hour
    By Ian Herndon in forum Excel Help
    Replies: 9
    Last Post: 07-07-2012, 10:04 PM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

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