# Using COUNTIFS on a VLOOKUP

#### andrewpennell

##### New Member
Hey Everyone.

I'm trying to extract data from the results of a VLOOKUP.

When I use
=countif(M3:M291,"<=60")
on values I copied from the VLOOKUP and pasted as values onto the same sheet, I get the correct answer.

However, when I try the very same formula but reference the range on the VLOOKUP, I get a much larger number.
This leads me to believe that the latter formula is counting hidden values.

Thus, my question is: How can I run a COUNT, AVERAGE, etc on a VLOOKUP table that a) excludes hidden values AND b) returns values based on a simple comparison (e.g., ">=5").

I'm aware that the SUBTOTAL function allows me to COUNT, AVERAGE, etc a range and exclude hidden values, however, I need to return values based on a simple comparison check (">=5", etc)

I'd really appreciate it if someone could also explain their solution by each attribute in whatever function they use.

Thanks for all the help!

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
When you refer to 'hidden values' in your table, is this achieved by filtering ?
If so, then you would need to include the filters as criteria in your countifs if you dont want the items filtered out to be counted.

To be honest, I'm not sure if a filter is being used. I just see that rows are not consecutive.

How is that table you just posted related to the original formula
=countif(M3:M291,"<=60")

?

"<=60" criteria doesn't make much sense on a range containing Text Strings..

The point of the picture was to illustrate how rows are being skipped. I have no idea why they're being skipped and couldn't find anything that's doing any filtering. The only formula on the page is a VLOOKUP formula. The content of those cells have nothing to do with the formula I'm trying to figure out. The picture was an answer to the question about 'hidden values'

The problem itself doesn't make any sense.
I don't see how you could get a different result simply by copy/pasting the range as values.

=countif(M3:M291,"<=60")
Should return the same result whether M3:M291 contains a Vlookup formula, or the resulting values after copy/paste special values.

How exactly do you do the Copy / Paste Special / Values process ?
Is there another step in your process that maybe removes 0's ?

Last edited:
So I highlighted the entire column and hit CTRL - C then pasted as values only.

When I try to reference the data from the VLOOKUP like this: =countif(Auto!J64:J2507,"<60") - the formula returns '257'.

When I reference data on the same sheet that I copied and pasted as values only, I get '60' using this formula:
=countif(M3:M291,"<=60")

Note: The first formula holds a range that obviously looks like it has more rows (J2507 vs. M291). This is odd and I have no idea why the count goes up like that, skipping numbers (as I illustrated with the previous picture).

When I try to reference the data from the VLOOKUP like this: =countif(Auto!J64:J2507,"<60") - the formula returns '257'.

When I reference data on the same sheet that I copied and pasted as values only, I get '60' using this formula: =countif(M3:M291,"<=60")

J64:J2507 contains ALOT more rows (2444) than M3:M291 (289)
So the 2 formulas are NOT looking at the same data.

Last edited:
So if you copied the ENTIRE column from Auto!J:J to Column M
Then this formula
=countif(M3:M291,"<=60")
Would be looking at values that were originally in Auto!J3:J291
So it is not the same as =countif(Auto!J64:J2507,"<60")

Replies
7
Views
246
Replies
3
Views
181
Replies
1
Views
249
Replies
3
Views
162
Replies
5
Views
142

1,196,048
Messages
6,013,095
Members
441,747
Latest member
darkman77

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

### Which adblocker are you using?

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

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