# VLOOKUP that Counts Question

#### BonusPlus

##### New Member
Hello Everybody,

I am hoping for some enlightenment. I’ve been scanning the forum and web for an answer and worked on a few combination formulas, but no luck...

I have a data table and want to vlookup and count simultaneously. Column B has various city names (Text), Column T has four options (example); cat, dog, cow or hen (Text). The city name appears more than once/multiple times, with varied column T inputs against each.</SPAN></SPAN>
I am wanting to return a numerical count of the number of times, the city name New York City has the option Dog in the corresponding column. It would be even better if I could use the string New York (rather than New York City – ie range lookup ‘False’)</SPAN></SPAN>
To put it into context further, I am then wanting to use this same formula in four different cells, to show me how many times ‘New York’ has the option Cat, how many times Dog, how many times Cow & how many times Hen. Thus every occurrence of New York in Column A is accounted for in the total sum.</SPAN></SPAN>

I have experimented with VLOOKUP, COUNTIF, and some others but can’t get it working</SPAN></SPAN></SPAN>
</SPAN>
I need a skilled mind to help me out here... </SPAN></SPAN>

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi, given in A1
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>
 city category New York City cow Salem hen Honolulu cow Helena hen Bismarck cat New York City hen Salem cow Honolulu dog Helena hen Bismarck hen Augusta cow New York City cow Salem cat Honolulu cat Helena hen Bismarck cow

<tbody>
</tbody>

in E1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>
 dog cat cow hen New York 0 0 2 1 Salem 0 1 1 1 Honolulu 1 1 1 0 Helena 0 0 0 3 Bismarck 0 1 1 1

<tbody>
</tbody>

formula in F2 is =COUNTIFS(\$A\$2:\$A\$17,\$E2&"*",\$B\$2:\$B\$17,F\$1) copied right and down till needed.
Would that work for you?

Absolute perfection Cyrilbrd. The animals of the world will be counted!

Last edited:
Absolute perfection Cyrilbrd. The animals of the world will be counted!

Glad it worked for you.
Thanks for the feedback and good luck...

Replies
5
Views
159
Replies
19
Views
246
Replies
3
Views
264
Replies
9
Views
111
Replies
3
Views
187

1,196,516
Messages
6,015,664
Members
441,914
Latest member
VBAllTheThings

### 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?

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