Using COUNTIFS on a VLOOKUP

andrewpennell

New Member
Joined
Mar 4, 2016
Messages
5
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Hercules1946

Well-known Member
Joined
Oct 6, 2007
Messages
545
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

New Member
Joined
Mar 4, 2016
Messages
5
To be honest, I'm not sure if a filter is being used. I just see that rows are not consecutive.
A3jqS
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

New Member
Joined
Mar 4, 2016
Messages
5
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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:

andrewpennell

New Member
Joined
Mar 4, 2016
Messages
5
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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