Lookup and Count help

RobExcel

New member
Joined
Dec 20, 2012
Messages
2
Reaction score
0
Points
0
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 CountryMiscMisc 1Misc 2Install Country
USAMiscMisc 1Misc 2Spain
USAMiscMisc 1Misc 2Spain
FranceMiscMisc 1Misc 2France
FranceMiscMisc 1Misc 2Morocco
FranceMiscMisc 1Misc 2Morocco
USAMiscMisc 1Misc 2USA
GermanyMiscMisc 1Misc 2USA
GermanyMiscMisc 1Misc 2Spain
USAMiscMisc 1Misc 2Spain

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
 

Canapone

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
Excel 2010
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:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 
Top