Lose the 0 in the Total cell

M

Malcolm

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
 
G

Gary''s Student

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

Malcolm

"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
 
G

Gord Dibben

Your copy and paste changed the format of the cell to Text.

Format to General then F2 and ENTER


Gord Dibben MS Excel MVP
 
M

Malcolm

"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
 
M

Malcolm

"Gary''s Student", Hi,
I am so sorry. I am not worthy. I kept trying and your solution worked. I
must have been doing something wrong, but you were right and I am a worm. LOL
Thanks dude,

Best regards,
Malcolm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Averaging a column that contains text 4
Averaging without #DIV0! 11
Linking Columns between worksheets 6
Help with my Lotto code please 10
SUMIFS help 3
If Statement to subtract “time†if condition met 7
Formula for totaling occurrences in a specified range 1
Help to calculate Drinking Fountains 2

Top