Results 1 to 3 of 3

Thread: Lookup and Count help

  1. #1

    Lookup and Count help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Hi, probably a pivot table could be the best way to get all you need. A formula could be:

    =COUNTIFS(D1: D10000,"USA",H1:H10000,"France")
    Last edited by Canapone; 2012-12-20 at 04:43 PM.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'd definitely go with the PivotTable. Just add the Order Country and Install Country as row labels, then drop the Install Country on as values. It should automatically convert it to count ofCountries and give you exactly what you need.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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