+ Reply to Thread
Results 1 to 15 of 15

Will VLookup work with a CountIfs formula?

  1. #1
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,144

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #5
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #6
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #7
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #8
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #10
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Will VLookup work with a CountIfs formula?

    Did you try this:

    Quote Originally Posted by Tony Valko View Post
    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. #12
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Will VLookup work with a CountIfs formula?

    Remove the quotes from E3

    e3 should just be
    <>EMCEP*

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #15
    Forum Contributor
    Join Date
    12-19-2013
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    201

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. COUNTIFS - why wont this work
    By karl.igniter in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-14-2013, 03:53 AM
  2. COUNTIFS OR COUNTIFS needs to work
    By joshbellfi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-23-2013, 10:55 AM
  3. [SOLVED] I can't get countifs to work.
    By jburl6 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2012, 03:50 AM
  4. COUNTIFS formula - why won't this work?!
    By Wabahoye in forum Excel General
    Replies: 1
    Last Post: 07-24-2012, 11:57 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM

Bookmarks

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