count results of formula, not text in formula

Giganews Newsgroups
Subject: count results of formula, not text in formula
Posted by:  Bill (bill.sh…@shaw.ca)
Date: 5 May 2004

I would like to count the results of a formula, format is a mix of numbers and text.

      A        B      Formula in Column B is
1      8    (8),(7)    ="("&A1&"),("&A3&")"
2    10      (10)    =+A2, with custom formatting of (0) to show brackets
3      7    (8),(10)  ="("&A1&"),("&A2&")"
4    14
5      9

There are numbers in Column A in rows 1 to 5, there are formulas in
Column B.  The actual formulas are the column I need to count the
requirements.  In this example, I need to count the occurrences of 8,
7, and 10.

Countif does not work as it looks for numbers or text not displayed values.
e.g.  COUNTIF(B1:B5,10) results in 1
      COUNTIF(B1:B5,"10") results in 1
      COUNTIF(B1:B5,"(10)") results in 0
      COUNTIF(B1:B5,"*(10)*") results in 1
      COUNTIF(B1:B5,"*10*") results in 1

There are actually two showing

Replies