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")
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
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 05:43 PM.
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
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
Bookmarks