count values in formula

B

Bill

I have a formula in cell K1 ="("&K6&"),("&K7&") that results in
(1),(11) as the value in K6 is 1 and in K7 is 11.

Other fomulas may ="("&K5&"),("&K7&") that results in (8),(11) where
the value in K5 is 8

I need to count in a column the number of occurances of 1, 8 and 11.

Countif does not work as it looks for numbers. I am thinking I have
to look for text in the value not the formula, but I don't know how to
do it.

You assistance is always appreciated.

Thanks

Bill
 
R

RagDyer

Countif *also* works with text!

Try these:

=COUNTIF(K1:K4,"*(1)*")
=COUNTIF(K1:K4,"*11*")
=COUNTIF(K1:K4,"*8*")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a formula in cell K1 ="("&K6&"),("&K7&") that results in
(1),(11) as the value in K6 is 1 and in K7 is 11.

Other fomulas may ="("&K5&"),("&K7&") that results in (8),(11) where
the value in K5 is 8

I need to count in a column the number of occurances of 1, 8 and 11.

Countif does not work as it looks for numbers. I am thinking I have
to look for text in the value not the formula, but I don't know how to
do it.

You assistance is always appreciated.

Thanks

Bill
 
B

Bill

That doesn't work. There are no text items of (1), there are only
formulas that create them as displayed values. For example in the
range b1 is +k10 where k10 has 1, and is formatted to show that way.
The others are text values with references to cells, not the actual
numbers either.

I have rewritten my request to try and clarify with a simple example.
Row 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 occurances 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

Thanks
 
D

Dave Peterson

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.
 
Top