Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Vlookup within a Countif...is this possible?

  1. #1
    New Member superakcraig's Avatar
    Join Date
    Oct 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup within a Countif...is this possible?

    Hello Everybody,

    I am hoping for some assistance. Here is what I have....

    I am trying to get counts of certain values.

    This is in the same workbook but different sheets.
    On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.

    An example of the formula is below...what am I missing?

    =COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))
    =COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))

    Thank you in advance for your assistance.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Vlookup within a Countif...is this possible?

    Try something like this...

    =SUMPRODUCT((Sheet2!B1:B1000="A")*(Sheet2!N1:N1000>=0)*(Sheet2!N1:N1000<=500))

    =SUMPRODUCT((Sheet2!B1:B1000="A")*(Sheet2!N1:N1000>500)*(Sheet2!N1:N1000<=1000))

    Note: you can't use a column reference like B:B with a Sumproduct formula.


    If you have Excel 2007 or later, you could use a COUNTIFS formula...

    =COUNTIFS(Sheet2!B:B,"A", Sheet2!N:N, ">=0", Sheet2!N:N, "<=500")
    =COUNTIFS(Sheet2!B:B,"A", Sheet2!N:N, ">500", Sheet2!N:N, "<=1000")
    Last edited by AlphaFrog; Oct 29th, 2010 at 06:18 PM.

  3. #3
    New Member superakcraig's Avatar
    Join Date
    Oct 2010
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup within a Countif...is this possible?

    AlphaFrog,

    Thank you so much. You are the bomb. It worked like a charm. You don't even know how much work this is going to save me.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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