# Count of rows based on result of vlookup

#### GMan777

##### New Member
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 !

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``=COUNT(FILTER(ROW(F2:F100),ISNUMBER(MATCH(F2:F100,FILTER(Regions[City],Regions[Country]="UK"),0))))``

#### GMan777

##### New Member
VBA Code:
``=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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
Excel Formula:
``=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

##### New Member

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

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

#### GMan777

##### New Member
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

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

Replies
6
Views
69
Replies
3
Views
188
Replies
4
Views
347
Replies
3
Views
497
Replies
14
Views
275

1,141,863
Messages
5,709,063
Members
421,611
Latest member
Lisa W

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back