Using COUNTIFS on a VLOOKUP

[andrewpennell]

Hey Everyone.

Thanks ahead of time for reading my post.

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!

 

[Hercules1946]

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.

 

[andrewpennell]

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

 

[andrewpennell]

 

[Jonmo1]

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

 

[andrewpennell]

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'

 

[Jonmo1]

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 ?

 

[andrewpennell]

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

 

[Jonmo1]

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.

 

[Jonmo1]

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")