ADDING COUNTIF TO AN INDIRECT VLOOKUP

[JuicyMusic]

Well, I've tried to add COUNTIF to an INDIRECT VLOOKUP formula. I've added "COUNTIF(" just before the VLOOKUP with no success.

Here is my formula that I need to add a COUNTIF added. I want the VLOOKUP results to tell me how many results did it find. Thank you so much!

=IF(K12="Off/Vacation","--",
VLOOKUP("*"&K12&"*",INDIRECT(K$8&"!M:M"),1,0))

 

[ScatmanKyle]

Could you show a bit of the dataset? From what I'm understanding, it's currently returning a single value using a vlookup. Are you trying to see how many items in the first column of the vlookup are present based off of what's entered into K12?

 

[JuicyMusic]

Hi, yes you are correct. It is returning a single value using a vlookup.
I have a Wildcard in the Vlookup so that it finds an employee name - no matter where (on a different tab) it is in that very long string (in column M:M)

I would like the vlookup to tell me how many a particular employee name occurs in column M

The K12 cell is where a specific employee name is - and the vlookup looks at a different tab for that same name in column M.

I hope the image is helpful. I can install the XL2bb. Thanks, Juicy

 

[ScatmanKyle]

Give this a shot. Core concept is your vlookup is returning the criteria for the countif, so you just need to put the countif function and range, then a comma (and closing bracket at the end) and you should be good.

=IF(K12="Off/Vacation","--",COUNTIF(M:M,VLOOKUP("*"&K12&"*",INDIRECT(K$8&"!M:M"),1,0)))

 

[JuicyMusic]

Hi, thank you for inserting the portion I needed. I couldn't figure out COUNTIF(M:M,. I also thought I needed to add a ",0" at the end.

What you did for me works but it needs to look at a different tab, so now it looks like this. I added the INDIRECT portion so that it looks at the other tab. See below.

This formula will give me a zero if that specific employee name does not exist on the other tab. It will also give me a "1" if that employee is there once - but it won't give me a "2" if that employee name is there twice in column M. Just so you know, we need to know how many times a name is on the other sheet. If it's five times, then I need to see a "5".

=IF(K12="Off/Vacation","--",
COUNTIF(INDIRECT(K$8&"!M:M"),VLOOKUP("*"&K12&"*",INDIRECT(K$8&"!M:M"),1,0)))

I think you've/we/ve almost got it. Juicy,

 

[JuicyMusic]

I SOLVED THIS ALL BY MYSELF!!!....? I inserted COUNTIFS because COUNTIF didn't work exactly. I inserted a wildcard and added that portion at the end of the formula.
I did not go online to find the answer. I just kept trying to figure out how to organize the sections of this formula.

Now this formula tells me how many times an employee name is found on the other tab in column M. I got rid of the VLOOKUP section.

=IF(K12="Off/Vacation","--",
COUNTIFS(INDIRECT(K$8&"!M:M"),"*"&K11&"*"))