# 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

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?

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.

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.

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

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

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

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

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

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

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.

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

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

Remove the quotes from E3

e3 should just be
<>EMCEP*

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)&"*")

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1