count
 

Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

count
 

Hi,

Try this array formula, see below on how to enter array formula. Change the
range to suit.

=COUNT(IF(ISERROR(SEARCH("anul",C1:C10)),IF(C1:C10 <"",1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"puiuluipui" wrote:

Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

count
 

One way:

=COUNTIF(C1:C20,"*")-COUNTIF(C1:C20,"*anul*")
--

HTH,

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


"puiuluipui" wrote in message
...
Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

count
 

One mo

=counta(a1:a20) - countif(a1:a20,"*(anul)*")



puiuluipui wrote:

Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

--

Dave Peterson

count
 

It's working!
Thanks allot!

"Dave Peterson" a scris:

One mo

=counta(a1:a20) - countif(a1:a20,"*(anul)*")



puiuluipui wrote:

Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

--

Dave Peterson

count
 

It's working!
Thanks allot!

"RagDyeR" a scris:

One way:

=COUNTIF(C1:C20,"*")-COUNTIF(C1:C20,"*anul*")
--

HTH,

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


"puiuluipui" wrote in message
...
Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!

count
 

It's working!
Thanks allot!

"Mike H" a scris:

Hi,

Try this array formula, see below on how to enter array formula. Change the
range to suit.

=COUNT(IF(ISERROR(SEARCH("anul",C1:C10)),IF(C1:C10 <"",1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"puiuluipui" wrote:

Hi, i need to count the number of cells in column "C", but without the cell
that contains (anul).
Ex:

C
John (ed)
John E
John (anul)
Mary M
Mary (anul)
Mary (em)

resut = 4
There are only two cells containing (anul), so the result must be 4. I need
the code in cell "F1".
Can this be done?
Thanks!