count values in formula

  • Thread starter Thread starter Bill
  • Start date Start date
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
 
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
 
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
 
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.
 
Back
Top