If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lose the 0 in the Total cell
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 |
#2
|
|||
|
|||
Lose the 0 in the Total cell
=IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s"))
-- Gary''s Student - gsnu201003 |
#3
|
|||
|
|||
Lose the 0 in the Total cell
"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,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")) -- Gary''s Student - gsnu201003 |
#4
|
|||
|
|||
Lose the 0 in the Total cell
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 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,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")) -- Gary''s Student - gsnu201003 |
#5
|
|||
|
|||
Lose the 0 in the Total cell
"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,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")) -- Gary''s Student - gsnu201003 |
#6
|
|||
|
|||
Lose the 0 in the Total cell
"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 "Gary''s Student" wrote: =IF(COUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")=0,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")) -- Gary''s Student - gsnu201003 |
#7
|
|||
|
|||
Lose the 0 in the Total cell
Gord, Hi,
Thanks big guy, right as usual. Best Regards, Malcolm "Gord Dibben" wrote: 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 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,"",C OUNTIF(B5:B22,"s")+COUNTIF(H5:H21,"s")) -- Gary''s Student - gsnu201003 . |
Thread Tools | |
Display Modes | |
|
|