Cells Count Totals Only for Non-VLookup #N/A

**garrett.grillo** · 04-27-2012, 06:22 PM

Hi Excel Forum Team,

I need help on an equation that will provide a total count for everything in a VLookup Iv'e done, but does not count the #N/A that VLookup provides when it doesn't pick up a match and other blank cells below the VLookup table.

Example:
-> Cell 1A is where the total cell count equation is.
-> Cell 2A thru. 11A is where the VLookup results are.
-> So, if the VLookup results come up with 8 matching results (ex.: John, Mark, Linda, etc.) in cells 2A thru. 9A and 2 non-matching results providing a #N/A in both 10A & 11A cells and the remaining blank cells below in 12A thru. infinity; the cell counter equation (in cell 1A) would only show a total of 8.

Thank you for reviewing my request for help!

Garrett

**martindwilson** · 04-27-2012, 06:40 PM

=counta(a2:a1000)-countif(a2:a1000,#n/a)
OR
=COUNTIF(A2:A2000,"<>#N/A")

**FDibbins** · 04-27-2012, 06:46 PM

a few points....

you should add =if(iserror(vlookup(your-lookup-formula)),0 (or whatever else you wat to show),vlookup(your-lookup-formula))
to count what you need, use =countif() eg =countif(range-you-want-to-count,

**garrett.grillo** · 04-27-2012, 06:50 PM

Hi Martindwilson,

Big Thank You!!! That equation did the trick!

Garrett

**garrett.grillo** · 04-29-2012, 08:59 PM

Hi FDibbins,

Big Thank You!!! That equation and help as well did the trick!

Garrett

**FDibbins** · 04-29-2012, 10:09 PM

you'r welcome. If this answered your question, please take a moment to mark the tread as "solved", and consider adding to the reputation of those who helped. Read points 2&3 below