I have a column in a spreadsheet that contains the result of a a lookup
function (refering to values in another spreadsheet) and displays a blank
field if the lookup does not find a match in the second spreadsheet.

I need to count how many matches are found (or not found). But when I use
the count function, it seems to be counting the formula and not the result of
the formula.

Does this make sense?

Example:

Formula of cells B10:B1500 = IFERROR((VLOOKUP(C45,'[Completed Property
List.xls]Sheet1'!$A$2:$B$10000,2,FALSE)),"")

- displays the contents of matching cell in column B of Completed Property
List if a match is found in column A, otherwise displays a empty cell.

Then I use the count function to count how many matches were found (or if
cells in the range B10:B1500 have anything in them besides the formula)

=COUNTIF(B10:B5000,"*")

Examples of contents of cells B10:B1500

Newcastle (result of formula)
Newcastle (result of formula)
(blank displayed, contains formula)
(blank displayed, contains formula)
Albury (result of formula)
Canberra (Manually entered text)
Albury (result of formula)

Thr result I would like to achieve is a count of 5 (or 2)


Newcastle

Try these...

This will count cells that contain *text only* and will *exclude* any cells
that contain formula blanks "".

=COUNTIF(B10:B5000,"?*")

To count the blank *and* empty cells:

=COUNTBLANK(B10:B500)

--
Biff
Microsoft Excel MVP


"jgupte" <(E-Mail Removed)> wrote in message
news:1D78D216-B32B-491E-950D-(E-Mail Removed)...
>I have a column in a spreadsheet that contains the result of a a lookup
> function (refering to values in another spreadsheet) and displays a blank
> field if the lookup does not find a match in the second spreadsheet.
>
> I need to count how many matches are found (or not found). But when I use
> the count function, it seems to be counting the formula and not the result
> of
> the formula.
>
> Does this make sense?
>
> Example:
>
> Formula of cells B10:B1500 = IFERROR((VLOOKUP(C45,'[Completed Property
> List.xls]Sheet1'!$A$2:$B$10000,2,FALSE)),"")
>
> - displays the contents of matching cell in column B of Completed Property
> List if a match is found in column A, otherwise displays a empty cell.
>
> Then I use the count function to count how many matches were found (or if
> cells in the range B10:B1500 have anything in them besides the formula)
>
> =COUNTIF(B10:B5000,"*")
>
> Examples of contents of cells B10:B1500
>
> Newcastle (result of formula)
> Newcastle (result of formula)
> (blank displayed, contains formula)
> (blank displayed, contains formula)
> Albury (result of formula)
> Canberra (Manually entered text)
> Albury (result of formula)
>
> Thr result I would like to achieve is a count of 5 (or 2)
>
>
> Newcastle
>
>
>