Lookup and Count help

[RobExcel]

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

[Canapone]

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")

[Ken Puls]

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.