Vlookup within a Countif...is this possible?

superakcraig

New Member
Joined
Oct 29, 2010
Messages
2
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,452
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:

superakcraig

New Member
Joined
Oct 29, 2010
Messages
2
:pray:AlphaFrog,:pray:

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.
 

Forum statistics

Threads
1,175,656
Messages
5,898,699
Members
434,723
Latest member
Epeople

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top