Count of rows based on result of vlookup

[GMan777]

I have my Data tab, where column F is a city (ex: London, Paris, etc).
I have a separate "Regions" table that has a list of cities in column A, and the corresponding country in column B.

I am looking for a formula that will count the number of rows in Tab1, where city in column C matches country "UK" in the table in Tab2.

I ended up with a countif that looks like this.. but realizing I'm asking to match "London" with "UK".. so result is zero.
=COUNTIFS(Data!F:F,VLOOKUP(Data!F:F,Regions[#All],2,False))

I am trying to get a formula that will give me the correct count directly, where I wouldn't need to add a column to my Data to populate the correct country for each row with a vlookup... then have a simple countif.
I actually have a 2nd condition, same situation (sites, with a table that associates sites to a company)... but once I have the logic down for one, countifs would allow to have both.

Or maybe I have to take another approach entirely.. looking for some guidance.

Thanks !

 

[Fluff]

How about

=COUNT(FILTER(ROW(F2:F100),ISNUMBER(MATCH(F2:F100,FILTER(Regions[City],Regions[Country]="UK"),0))))

 

[GMan777]

How about

=COUNT(FILTER(ROW(F2:F100),ISNUMBER(MATCH(F2:F100,FILTER(Regions[City],Regions[Country]="UK"),0))))

Hi.. first off thanks so much for the quick response.

This does work, but seeing that it's not a countifs... am now wondering how I add a second similar condition to get the count of rows only if both are met:
the city (F column) matches the "UK" country (Regions table) AND the site (G column) matches company "ABC" (Companies table).

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[GMan777]

I can't install the add-in.. but created a sample file with images below, with:
- the summary table where I need the formulas to give the count of parts made by region/company
- the Data tab with raw data
- the Regions & Companies tables that map the data to the groupings I need for my summary

Hopefully this gives a clear enough picture?

This initial solution above works if I only needed to map the Region, but since I need to consider a 2nd condition.. unsure how to adapt it.

 

[Fluff]

How about like

=COUNT(FILTER(ROW(B2:B10),(ISNUMBER(MATCH(A2:A10,FILTER(F8:F11,G8:G11="Ford"),0)))*(ISNUMBER(MATCH(B2:B10,FILTER(F2:F5,G2:G5="UK"),0)))))

yu'll have to change the ranges to your table references

 

[GMan777]

Thank you! The solution above works!
I will mention that this formula seems quite resource intensive, Excel kept freezing for a few minutes at a time just when I copied the formula to a different cell.
I was then asked to ask yet another parameter to these results, at which point I just gave up on trying to combine it all in one monster formula.. at this point better to add a few vlookup columns to my data, then have the option to either use simply countifs, or just use a pivot.

But I will keep this formula in my back pocket, thanks for the education

 

[Fluff]

That formula should not be resource intensive unless you are using whole column references.
But glad you've sorted it out.

 

[GMan777]

That formula should not be resource intensive unless you are using whole column references.
But glad you've sorted it out.

Educated again.. thanks for that last comment, I indeed was using whole column references to avoid re-sizing my formulas every time I update my raw data... I guess having the raw data as a table would serve same purpose and avoid the resource issue.

 

[Fluff]

That would be a lot better than using whole columns, which will slow virtually every formula to a crawl.