A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting Results of Formulas



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2010, 12:01 AM posted to microsoft.public.excel.worksheet.functions
jgupte
external usenet poster
 
Posts: 2
Default 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



  #2  
Old April 30th, 2010, 12:15 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.