Counting Results of Formulas
 

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

Counting Results of Formulas
 

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" wrote in message
...
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