# Will VLookup work with a CountIfs formula?

1. ## Will VLookup work with a CountIfs formula?

I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using: ``Please Login or Register  to view this content.``
Before I add the Vlookup, I get a number of 50. I should get at least 40+, but after adding the ``Please Login or Register  to view this content.``
I get "0."

I'm wondering if I need to use VLookup differently in this case ????

Thanks for any help!

David  Register To Reply

2. ## Re: Will VLookup work with a CountIfs formula?

The VLOOKUP should work.

Put the VLOOKUP is its own cell, say A1, then try this formula:

=COUNTIF(Database!J:J,A1)

What result do you get?  Register To Reply

3. ## Re: Will VLookup work with a CountIfs formula?

Hi,

Vlookup will only return a value from a range. If you need to count the rows the you need to use either COUNTIFS() as you are doing or perhaps =COUNTA which counts the number of cells in a rnage that are not blank.  Register To Reply

4. ## Re: Will VLookup work with a CountIfs formula?

I think they're using the VLOOKUP as the criteria for Database!J:J.

That's how the formula is evaluating it.  Register To Reply

5. ## Re: Will VLookup work with a CountIfs formula?

Tony V, that's correct. I'm trying to use the Value ( not the formula) returned from the Vlookup ( as u can see by lookup there are only two options) as the criteria for counting rows in the range of database!\$J:\$J

Sent from my iPhone using Tapatalk  Register To Reply

6. ## Re: Will VLookup work with a CountIfs formula?

Tony V. I had already tried your suggestion of putting the VLookup formula in a separate cell and then referencing that cell in the countis formula, however I still get a result of zero

Sent from my iPhone using Tapatalk  Register To Reply

7. ## Re: Will VLookup work with a CountIfs formula?

I was wondering if the countifs formula is looking at the resulting cell formula rather than the cell value ???

Sent from my iPhone using Tapatalk  Register To Reply

8. ## Re: Will VLookup work with a CountIfs formula?

Also I'm wondering if I have my vlook up formula is correct as the result of lookup is ending in the same value rather than different values

Sent from my iPhone using Tapatalk  Register To Reply

9. ## Re: Will VLookup work with a CountIfs formula?

Ok, that means there are no matches of the result of the VLOOKUP that can be found in Database!\$J:\$J.

See this for common data problems:

http://contextures.com/xlFunctions02.html#Trouble  Register To Reply

10. ## Re: Will VLookup work with a CountIfs formula?

Of course that would be my assumption also except that when I go to the database or worksheet there are, at least 40 of them

Sent from my iPhone using Tapatalk  Register To Reply

11. ## Re: Will VLookup work with a CountIfs formula?

Did you try this: Originally Posted by Tony Valko The VLOOKUP should work.

Put the VLOOKUP is its own cell, say A1, then try this formula:

=COUNTIF(Database!J:J,A1)

What result do you get?
If you get a result of 0 then you have a "data problem".

It's kind of hard to troubleshoot without seeing the file.  Register To Reply

12. ## Re: Will VLookup work with a CountIfs formula?

Tony,

I'm guessing the problem is in the E3 reference value itself. I've included two screenshots below, one with "<>EMCEP*" as the criteria, and one with the VLookup Reference cell E3 as the criteria. With the actual text, i get a result of 334, with the E3 cell reference I get 0. That's where my conflict lies. It's the exact same "<>EMCEP*" value, but, i think, the formula is looking at E3 as something other than text/value.

Attachment 327338

Attachment 327339  Register To Reply

13. ## Re: Will VLookup work with a CountIfs formula?

Remove the quotes from E3

e3 should just be
<>EMCEP*  Register To Reply

14. ## Re: Will VLookup work with a CountIfs formula?

If you have this in the cell (quotes included):

"<>EMCEP*"

The formula is evaluating that as the literal string "<>EMCEP*".

If you remove the quotes:

<>EMCEP*

Then it gets evaluated as:

"Is not equal to EMCEP followed by any characters or no characters".

My preference would be to just use the EMCEP then add the operators in the formula.

VLOOKUP(K1,ListSheet!F2:G3,2,0) returns EMCEP

Then:

=COUNTIFS(Database!\$B:\$B,">="& DATEVALUE(G\$2 & "1, " & \$B\$1), Database!\$B:\$B,"<="&EOMONTH(DATEVALUE(G\$2 & "1, " & \$B\$1),0),Database!\$G:\$G, \$B3, Database!J:J,"<>"&VLOOKUP(K1,ListSheet!F2:G3,2,0)&"*")  Register To Reply

15. ## Re: Will VLookup work with a CountIfs formula?

Thanks Tony for the input. i'll try this tmrw when i have time. so, my use of the VLookup was correct but the notation was wrong. that's what i was looking for. I'll let you know how it goes.

Sent from my iPad using Tapatalk  Register To Reply