VLOOKUP that Counts Question

[BonusPlus]

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>

 

[cyrilbrd]

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.
Adjust ranges to fit your query.
Would that work for you?

 

[BonusPlus]

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

 

[cyrilbrd]

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

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