Prev: File placement with copied sheets
Next: Difference between two date/time values
From: Malcolm on 31 May 2010 10:33
I'm using the formula below, in a worksheet to count cells containing the
letter S. My problem is in the total cell, it displays a 0 until I enter an S
in the cells it is totaling. Is there a way I can modify the formula so that
it does not display the 0? I'm using Excel 2007, but the formula must work in
Excel 2003.

=COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")

Thanks,
Malcolm

From: Gary''s Student on 31 May 2010 10:46
=IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))

--
Gary''s Student - gsnu201003
From: Malcolm on 31 May 2010 11:15
"Gary''s Student", Hi,
I copied and pasted your solution into the formula bar and all it returned
was the formula itself in the Total cell. I then tried to enter the formula
myself and got the same results. Sorry dude, this didn't work.

Regards,
Malcolm

"Gary''s Student" wrote:

> =IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))
>
> --
> Gary''s Student - gsnu201003
From: Gord Dibben on 31 May 2010 12:28
Your copy and paste changed the format of the cell to Text.

Format to General then F2 and ENTER


Gord Dibben MS Excel MVP

On Mon, 31 May 2010 08:15:01 -0700, Malcolm
<Malcolm(a)discussions.microsoft.com> wrote:

>"Gary''s Student", Hi,
>I copied and pasted your solution into the formula bar and all it returned
>was the formula itself in the Total cell. I then tried to enter the formula
>myself and got the same results. Sorry dude, this didn't work.
>
>Regards,
>Malcolm
>
>"Gary''s Student" wrote:
>
>> =IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))
>>
>> --
>> Gary''s Student - gsnu201003

From: Malcolm on 31 May 2010 12:55
"Gary''s Student", Hi,
Please forgive me. I am not worthy. I got it to work. You were right and I
am a worm. LOL

Thank you,
Best regards,
Malcolm

"Malcolm" wrote:

> "Gary''s Student", Hi,
> I copied and pasted your solution into the formula bar and all it returned
> was the formula itself in the Total cell. I then tried to enter the formula
> myself and got the same results. Sorry dude, this didn't work.
>
> Regards,
> Malcolm
>
> "Gary''s Student" wrote:
>
> > =IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))
> >
> > --
> > Gary''s Student - gsnu201003
 |  Next  |  Last
Pages: 1 2
Prev: File placement with copied sheets
Next: Difference between two date/time values