count results of formula, not text in formula

[Bill]

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

 

[Frank Kabel]

Hi
the format of your second row is not seen by COUNTIF. That is the value
in B2: (10) is internally stored as 10. So looking for (10) won't find
this value. So you have to use
=COUNTIF(B1:B5,"*(10)*")+COUNTIF(B1:B5,"10")

 

[Dave Peterson]

You didn't like my response from a couple of weeks ago?

http://groups.google.com/[email protected]

 

[Bill]

Dave,

I did a search of the group and only found the first reply, which
didn't work, so I replied to it, and couldn't find your reply, only
the first one. There must be a way to find subsequent posts, but I
missed out.

 

[Dave Peterson]

My post showed up at the bottom of the thread when I clicked on this link:

http://groups.google.com/[email protected]

Here it is again:

Your formulas returned:

-- ------- -- ------------------------
8 (8),(7) 0 =COUNTIF(B1:B5,10)
10 (10) 0 =COUNTIF(B1:B5,"10")
7 (8),(10) 1 =COUNTIF(B1:B5,"(10)")
14 2 =COUNTIF(B1:B5,"*(10)*")
9 2 =COUNTIF(B1:B5,"*10*")

When column B was formatted as text. If I want to count the number of -10's (no
matter how they're formatted, I could use:

=SUMPRODUCT(--(B1:B5=-10))
(This ignores the text -10's.)

I think I'd use the sum of a couple of formulas if I wasn't sure what was in
those cells (and how they were formatted):

=COUNTIF(B1:B5,"*(10)*")+SUMPRODUCT(--(B1:B5=-10))

I used that formula when (10) was text and when it was -10 (formatted) and got 2
both times.