Thanks:  0
Likes:  0

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

1. ## 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))

2. ## 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")

3. ## 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.

## User Tag List

#### Posting Permissions

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