Counting Results of Formulas
in [Functions]
|
Prev: search excel for overpopulated cells
Next: Adding fill to cells only when value is "No" in another cell
From: jgupte on 29 Apr 2010 19:01 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
From: T. Valko on 29 Apr 2010 19:15
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" <jgupte(a)discussions.microsoft.com> wrote in message news:1D78D216-B32B-491E-950D-B7AEB5E311C3(a)microsoft.com... >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 > > > |