Using Count Formula in a Range

[ConfusedExcelGuy]

I am using a VLOOKUP in a range of cells on a table with IFERROR so that only results are visible. I want to count only the results. If I try to use COUNTA or SUBTOTAL I get back the total range count since the formula is in all the cells. Is there a way to count only the cells with visible data (a hit) without copying and pasting the whole range as values?

 

[Alex Blakenburg]

Since you have MS365 this should work:

=SUM(--(A3:A6<>""))

For older versions

=SUMPRODUCT(--(A3:A6<>""))

 

[ConfusedExcelGuy]

Since you have MS365 this should work:

=SUM(--(A3:A6<>""))

For older versions

=SUMPRODUCT(--(A3:A6<>""))

I am using MS365. I just tried these formulas. I am still getting back the total row count...

 

[ConfusedExcelGuy]

The data in the column is more like a serial number a mixture of letters and numbers with no space between them. It amazes me how difficult this is.

 

[Alex Blakenburg]

Show us a sample of your data including the row and column references. Did you change the A3:A6 to your actual references ?
What I gave you should work whether it is Text or Numbers ?

 

[Fluff]

Also what is the exact vlookup formula that you are using?

 

[ConfusedExcelGuy]

Show us a sample of your data including the row and column references. Did you change the A3:A6 to your actual references ?
What I gave you should work whether it is Text or Numbers ?

Thank you Alex! Was able to get these to work today. Not sure why they wouldn't work yesterday night.

I can leave the formula and count only the visible results.

 

[ConfusedExcelGuy]

Also what is the exact vlookup formula that you are using?

How can I count the empty cells only?
The formula is +IFERROR(+VLOOKUP(F7,INDEX!A:A,1,0),"") , where F7 is a column of reference numbers 14 characters long: ####AB####C## , and INDEX is a sheet with a system report.

 

[Alex Blakenburg]

If you swap the <> for = that should count where the IfError is returning "".
If you want to include in the count where the VLookup is returning "" then you need to change the lookup to

=IFERROR(VLOOKUP(F7,INDEX!A:A,1,0) & "", "")