Need a function to remove COUNTIF data

  • Thread starter Thread starter Always Care Adult Center
  • Start date Start date
A

Always Care Adult Center

I don't know if this is even possible, but I have a spreadsheet that counts
instances of text characters, like so for example:

=COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p")

Now this goes on for several pages and in some cases there are no occurences
of these characters, so the function writes 0 in the Total column. This is a
bit of an annoyance and so I was wondering if it was at all possible to add
another function, or to somehow modify the existing function, so the Total
space would be left blank. I tried substituting 0 with a space but that
didn't work. Maybe I'm doing it wrong...

Thanks for any help.
 
Perhaps using Tools=>Options=>View and unchecking Zero Values would be the
easiest solution.
 
Try this very simple mod

=IF(COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p")=0,"",COUNTIF(C3:AG3,"a")+COUNTIF
(C3:AG3,"p"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
try putting an if statement around the formula, like:

IF(COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p")<>0,COUNTIF(C3:AG3,"a")+COUNTIF(C3
:AG3,"p"),"")

or you can go to conditional formatting, and set the font format color equal
to the color of the background when cell value is 0.
 
Thank you for a fast response, that did it. I was looking for the hard
way... Just out of curiousity, is it possible to do what I wanted using
functions?
 
Oops, never mind. The people here are so quick it's amazing. Thanks to
everybody who's responded, it's very helpful.
 
...
...
=IF(COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p")=0,"",
COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p"))
Click to expand...
...

While the second sum of COUNTIFs is needed, the first could be replaced by a
single OR.

=IF(OR(C3:AG3,{"a";"p"}),COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p"),"")
 
Harlan,

Small typo

=IF(OR(C3:AG3={"a";"p"}),COUNTIF(C3:AG3,"a")+COUNTIF(C3:AG3,"p"),"")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top