countif clarification

Thank you for earlier response... have the formula now to count blank cells.

Been perusing your other answers but now have this question...

Scenario: one column, c4:c34. Will contain some numbers, some zeros, and
some necessary blank rows in case I add more information (either zero's or
numbers). Realized I didn't need to count the blank rows, just the zeros and
the numbers.

I can now count all the cells with zero's. How do I count the cells that
are greater than zero? I've tried a variety of formulas but my guess'n golly
hasn't eureka'd yet!

Linda

Have you tried

=COUNTIF(C4:C34,">0")

Yes, I've tried that and other variations from the discussion group answers
that seemed logical... well for the questions posed they were...

Anyway...That one you show returns a value of "0". In that column of 31
cells, two contain numbers, 3 contain zeros, and the rest are blank. I can
count the zero's, I can count the blanks, but counting the numbered cells is
..... well .... I'm drawing a blank.

"goto_guy" wrote:

>
> Have you tried
> =COUNTIF(C4:C34,">0")
>
>
> --
> goto_guy
> ------------------------------------------------------------------------
> goto_guy's Profile: http://www.excelforum.com/member.php...o&userid=30557
> View this thread: http://www.excelforum.com/showthread...hreadid=528316
>
>

Then it's simple, they must be text.


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"LInda" <LInda@discussions.microsoft.com> wrote in message
news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> Yes, I've tried that and other variations from the discussion group
> answers
> that seemed logical... well for the questions posed they were...
>
> Anyway...That one you show returns a value of "0". In that column of 31
> cells, two contain numbers, 3 contain zeros, and the rest are blank. I
> can
> count the zero's, I can count the blanks, but counting the numbered cells
> is
> .... well .... I'm drawing a blank.
>
> "goto_guy" wrote:
>
>>
>> Have you tried
>> =COUNTIF(C4:C34,">0")
>>
>>
>> --
>> goto_guy
>> ------------------------------------------------------------------------
>> goto_guy's Profile:
>> http://www.excelforum.com/member.php...o&userid=30557
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=528316
>>
>>

Sounds like your numbers are being stored as text. If this returns a value

=SUMPRODUCT(--(C4:C34>0))

that will confirm it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"LInda" <LInda@discussions.microsoft.com> wrote in message
news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> Yes, I've tried that and other variations from the discussion group
answers
> that seemed logical... well for the questions posed they were...
>
> Anyway...That one you show returns a value of "0". In that column of 31
> cells, two contain numbers, 3 contain zeros, and the rest are blank. I
can
> count the zero's, I can count the blanks, but counting the numbered cells
is
> .... well .... I'm drawing a blank.
>
> "goto_guy" wrote:
>
> >
> > Have you tried
> > =COUNTIF(C4:C34,">0")
> >
> >
> > --
> > goto_guy
> > ------------------------------------------------------------------------
> > goto_guy's Profile:
http://www.excelforum.com/member.php...o&userid=30557
> > View this thread:
http://www.excelforum.com/showthread...hreadid=528316
> >
> >

Sorry, that formula returned a value of 5 which is counting the 2 cells with
numbers plus the 3 cells with zeros.
Not the right one yet.... need it to return a value of 2, and then that'll
probably be the right formula. What could be simpler than counting anything
greater than 0? The answer's here somewhere.
Linda

"Bob Phillips" wrote:

> Sounds like your numbers are being stored as text. If this returns a value
>
> =SUMPRODUCT(--(C4:C34>0))
>
> that will confirm it.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "LInda" <LInda@discussions.microsoft.com> wrote in message
> news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> > Yes, I've tried that and other variations from the discussion group
> answers
> > that seemed logical... well for the questions posed they were...
> >
> > Anyway...That one you show returns a value of "0". In that column of 31
> > cells, two contain numbers, 3 contain zeros, and the rest are blank. I
> can
> > count the zero's, I can count the blanks, but counting the numbered cells
> is
> > .... well .... I'm drawing a blank.
> >
> > "goto_guy" wrote:
> >
> > >
> > > Have you tried
> > > =COUNTIF(C4:C34,">0")
> > >
> > >
> > > --
> > > goto_guy
> > > ------------------------------------------------------------------------
> > > goto_guy's Profile:
> http://www.excelforum.com/member.php...o&userid=30557
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=528316
> > >
> > >
>
>
>

Oh, and I reconfirmed that the row of cells c4:c34 are formatted as number,
not text. No difference in the result.

"LInda" wrote:

> Sorry, that formula returned a value of 5 which is counting the 2 cells with
> numbers plus the 3 cells with zeros.
> Not the right one yet.... need it to return a value of 2, and then that'll
> probably be the right formula. What could be simpler than counting anything
> greater than 0? The answer's here somewhere.
> Linda
>
> "Bob Phillips" wrote:
>
> > Sounds like your numbers are being stored as text. If this returns a value
> >
> > =SUMPRODUCT(--(C4:C34>0))
> >
> > that will confirm it.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "LInda" <LInda@discussions.microsoft.com> wrote in message
> > news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> > > Yes, I've tried that and other variations from the discussion group
> > answers
> > > that seemed logical... well for the questions posed they were...
> > >
> > > Anyway...That one you show returns a value of "0". In that column of 31
> > > cells, two contain numbers, 3 contain zeros, and the rest are blank. I
> > can
> > > count the zero's, I can count the blanks, but counting the numbered cells
> > is
> > > .... well .... I'm drawing a blank.
> > >
> > > "goto_guy" wrote:
> > >
> > > >
> > > > Have you tried
> > > > =COUNTIF(C4:C34,">0")
> > > >
> > > >
> > > > --
> > > > goto_guy
> > > > ------------------------------------------------------------------------
> > > > goto_guy's Profile:
> > http://www.excelforum.com/member.php...o&userid=30557
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=528316
> > > >
> > > >
> >
> >
> >

It wasn't meant to be an alternative, just confirmed they are text, which it
did. Change them to General and F2 the text cells.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"LInda" <LInda@discussions.microsoft.com> wrote in message
news:D332E203-6461-4763-AB1B-B857ACF28C10@microsoft.com...
> Sorry, that formula returned a value of 5 which is counting the 2 cells
with
> numbers plus the 3 cells with zeros.
> Not the right one yet.... need it to return a value of 2, and then that'll
> probably be the right formula. What could be simpler than counting
anything
> greater than 0? The answer's here somewhere.
> Linda
>
> "Bob Phillips" wrote:
>
> > Sounds like your numbers are being stored as text. If this returns a
value
> >
> > =SUMPRODUCT(--(C4:C34>0))
> >
> > that will confirm it.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "LInda" <LInda@discussions.microsoft.com> wrote in message
> > news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> > > Yes, I've tried that and other variations from the discussion group
> > answers
> > > that seemed logical... well for the questions posed they were...
> > >
> > > Anyway...That one you show returns a value of "0". In that column of
31
> > > cells, two contain numbers, 3 contain zeros, and the rest are blank.
I
> > can
> > > count the zero's, I can count the blanks, but counting the numbered
cells
> > is
> > > .... well .... I'm drawing a blank.
> > >
> > > "goto_guy" wrote:
> > >
> > > >
> > > > Have you tried
> > > > =COUNTIF(C4:C34,">0")
> > > >
> > > >
> > > > --
> > > > goto_guy
> > >
> ------------------------------------------------------------------------
> > > > goto_guy's Profile:
> > http://www.excelforum.com/member.php...o&userid=30557
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=528316
> > > >
> > > >
> >
> >
> >

It doesn't matter if they are formatted as numbers, excel sees them as text,
no formatting will change that, you will need to calculate them if they
don't have trailing or leading spaces..

Try by copying an empty cell, select the range and do edit>paste special and
select add..

then use the countif function

If that doesn't work you have invisible characters like html char(160) or
spaces in the cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"LInda" <LInda@discussions.microsoft.com> wrote in message
news:AA3861E7-3676-4EA5-B5CD-1DBE48E6337F@microsoft.com...
> Oh, and I reconfirmed that the row of cells c4:c34 are formatted as
> number,
> not text. No difference in the result.
>
> "LInda" wrote:
>
>> Sorry, that formula returned a value of 5 which is counting the 2 cells
>> with
>> numbers plus the 3 cells with zeros.
>> Not the right one yet.... need it to return a value of 2, and then
>> that'll
>> probably be the right formula. What could be simpler than counting
>> anything
>> greater than 0? The answer's here somewhere.
>> Linda
>>
>> "Bob Phillips" wrote:
>>
>> > Sounds like your numbers are being stored as text. If this returns a
>> > value
>> >
>> > =SUMPRODUCT(--(C4:C34>0))
>> >
>> > that will confirm it.
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from the email address if mailing direct)
>> >
>> > "LInda" <LInda@discussions.microsoft.com> wrote in message
>> > news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
>> > > Yes, I've tried that and other variations from the discussion group
>> > answers
>> > > that seemed logical... well for the questions posed they were...
>> > >
>> > > Anyway...That one you show returns a value of "0". In that column of
>> > > 31
>> > > cells, two contain numbers, 3 contain zeros, and the rest are blank.
>> > > I
>> > can
>> > > count the zero's, I can count the blanks, but counting the numbered
>> > > cells
>> > is
>> > > .... well .... I'm drawing a blank.
>> > >
>> > > "goto_guy" wrote:
>> > >
>> > > >
>> > > > Have you tried
>> > > > =COUNTIF(C4:C34,">0")
>> > > >
>> > > >
>> > > > --
>> > > > goto_guy
>> > > > ------------------------------------------------------------------------
>> > > > goto_guy's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30557
>> > > > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=528316
>> > > >
>> > > >
>> >
>> >
>> >

EUREKA!
It worked.
I don't understand how, but all I need is the right formula for now.
Linda

"Peo Sjoblom" wrote:

> It doesn't matter if they are formatted as numbers, excel sees them as text,
> no formatting will change that, you will need to calculate them if they
> don't have trailing or leading spaces..
>
> Try by copying an empty cell, select the range and do edit>paste special and
> select add..
>
> then use the countif function
>
> If that doesn't work you have invisible characters like html char(160) or
> spaces in the cells
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "LInda" <LInda@discussions.microsoft.com> wrote in message
> news:AA3861E7-3676-4EA5-B5CD-1DBE48E6337F@microsoft.com...
> > Oh, and I reconfirmed that the row of cells c4:c34 are formatted as
> > number,
> > not text. No difference in the result.
> >
> > "LInda" wrote:
> >
> >> Sorry, that formula returned a value of 5 which is counting the 2 cells
> >> with
> >> numbers plus the 3 cells with zeros.
> >> Not the right one yet.... need it to return a value of 2, and then
> >> that'll
> >> probably be the right formula. What could be simpler than counting
> >> anything
> >> greater than 0? The answer's here somewhere.
> >> Linda
> >>
> >> "Bob Phillips" wrote:
> >>
> >> > Sounds like your numbers are being stored as text. If this returns a
> >> > value
> >> >
> >> > =SUMPRODUCT(--(C4:C34>0))
> >> >
> >> > that will confirm it.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> > "LInda" <LInda@discussions.microsoft.com> wrote in message
> >> > news:30A18817-1136-479B-A976-4EB8455F5A50@microsoft.com...
> >> > > Yes, I've tried that and other variations from the discussion group
> >> > answers
> >> > > that seemed logical... well for the questions posed they were...
> >> > >
> >> > > Anyway...That one you show returns a value of "0". In that column of
> >> > > 31
> >> > > cells, two contain numbers, 3 contain zeros, and the rest are blank.
> >> > > I
> >> > can
> >> > > count the zero's, I can count the blanks, but counting the numbered
> >> > > cells
> >> > is
> >> > > .... well .... I'm drawing a blank.
> >> > >
> >> > > "goto_guy" wrote:
> >> > >
> >> > > >
> >> > > > Have you tried
> >> > > > =COUNTIF(C4:C34,">0")
> >> > > >
> >> > > >
> >> > > > --
> >> > > > goto_guy
> >> > > > ------------------------------------------------------------------------
> >> > > > goto_guy's Profile:
> >> > http://www.excelforum.com/member.php...o&userid=30557
> >> > > > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=528316
> >> > > >
> >> > > >
> >> >
> >> >
> >> >
>
>
>