Lookup and Count Using Pivot Table

[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

[Admin]

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.

[Excel Fox]

In case you are struggling to figure out how to do that, here's an example showing what Admin explained above.