I'm trying to count the rows of a worksheet based on the location selection. Heres what I'm using:
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."Please Login or Register to view this content.
I'm wondering if I need to use VLookup differently in this case ????
Thanks for any help!
David
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?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I think they're using the VLOOKUP as the criteria for Database!J:J.
That's how the formula is evaluating it.
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
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
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
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
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
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
Did you try this:
If you get a result of 0 then you have a "data problem".Originally Posted by Tony Valko
It's kind of hard to troubleshoot without seeing the file.
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
Remove the quotes from E3
e3 should just be
<>EMCEP*
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)&"*")
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks