Count of rows based on result of vlookup

GMan777

New Member
Joined
Dec 1, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
=COUNT(FILTER(ROW(F2:F100),ISNUMBER(MATCH(F2:F100,FILTER(Regions[City],Regions[Country]="UK"),0))))
 

GMan777

New Member
Joined
Dec 1, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 1, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

1626980948668.png


1626980975698.png
1626980993184.png
1626981008785.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
How about like
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
 
Solution

GMan777

New Member
Joined
Dec 1, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
That formula should not be resource intensive unless you are using whole column references.
But glad you've sorted it out.
 

GMan777

New Member
Joined
Dec 1, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,666
Office Version
  1. 365
Platform
  1. Windows
That would be a lot better than using whole columns, which will slow virtually every formula to a crawl.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top