Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / October 2006

Tip: Looking for answers? Try searching our database.

COUNTIF?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Marcus Fox - 11 Oct 2006 22:08 GMT
Basically I have 13 cells spread out over a spreadsheet that normally have
zero values. What I want to do is count the number of them that have
non-zero (actually will be greater than zero) values and return that value
in another cell. What I was thinking of was using COUNTIF, but that only
seems to work for a contiguous range of cells. =COUNTIF (A1:H1, "<0") will
count the number of cells from A1 to H1 that have a value greater than zero.
My problem is that the cells are non-contiguous. For example =COUNTIF(A1,
C1, H1, F1, K1, L1, O1, "<0") does not work. Anyone help with an
alternative?

Many thanks

Marcus
Reply to this Message
Bob Phillips - 11 Oct 2006 22:34 GMT
=SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Basically I have 13 cells spread out over a spreadsheet that normally have
> zero values. What I want to do is count the number of them that have
[quoted text clipped - 9 lines]
>
> Marcus
Reply to this Message
Ragdyer - 11 Oct 2006 23:57 GMT
And once again Sumproduct() is on the mind of our illustrious Mr.
Philips!<g>

Hey Bob, you should remember to always try Sum() first.<vbg>
Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))
>
[quoted text clipped - 12 lines]
> >
> > Marcus
Reply to this Message
Bob Phillips - 12 Oct 2006 00:10 GMT
And how does SUM COUNT the cells with a value >0?

And the name is Phillips!

Bob Phillips

> And once again Sumproduct() is on the mind of our illustrious Mr.
> Philips!<g>
[quoted text clipped - 20 lines]
> > >
> > > Marcus
Reply to this Message
Ragdyer - 12 Oct 2006 03:11 GMT
I apologize for the misspelling, but I don't think that I've ever addressed
you by your last name before.
Considering I don't misspell Debra's last name, there's really no excuse for
getting yours wrong.

And I was referring to Sum() instead of Sumproduct():

=SUM(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> And how does SUM COUNT the cells with a value >0?
>
[quoted text clipped - 35 lines]
> > > >
> > > > Marcus
Reply to this Message
Bob Phillips - 12 Oct 2006 08:55 GMT
I'm a bit touchy about my name because everyone spells it with just one l,
aka the Dutch multi-national, and I hate that, my name is Welsh, and proud
of it.

Didn't appreciate the point you were making about Sum, I though you had
mis-read and meant

=SUM(A1,C1,H1,F1,K1,L1,O1)

not just SUM instead of SUMPRODUCT. I think you have made this point to me
before, I will try and do better in future <bg>

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I apologize for the misspelling, but I don't think that I've ever addressed
> you by your last name before.
[quoted text clipped - 22 lines]
>
> --------------------------------------------------------------------------

> > -
> > > > =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}),
[quoted text clipped - 22 lines]
> > > > >
> > > > > Marcus
Reply to this Message
Roger Govier - 12 Oct 2006 10:18 GMT
Hi Bob

>my name is Welsh, and proud of it.
Well done Bob!!!

Cymru am byth!

Signature

Regards

Roger Govier

> I'm a bit touchy about my name because everyone spells it with just
> one l,
[quoted text clipped - 85 lines]
>> > > > >
>> > > > > Marcus
Reply to this Message
RagDyeR - 12 Oct 2006 16:40 GMT
You're right Bob, we did have an exchange on this very subject of Sum() and
Sumproduct(), and that's why I attempted a frivolous comment as a reminder,
and ended up sticking my foot in my mouth.

But, now knowing of your Welsh origin, and the relative simplicity of your
name compared to Debra's, which I'm sure is of Scottish heritage, I
definitely don't wish to even put forward the APPEARANCE of favoring one
part of that island of yours over the other.<bg>

Actually, the complexity of her name (Dalgleish) sort of helps insure it's
proper spelling, since one *must* think about it while typing, where as the
simplicity of yours instills no such perceived need to be careful, that is
... up until now!<g>
Signature


Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I'm a bit touchy about my name because everyone spells it with just one l,
aka the Dutch multi-national, and I hate that, my name is Welsh, and proud
of it.

Didn't appreciate the point you were making about Sum, I though you had
mis-read and meant

=SUM(A1,C1,H1,F1,K1,L1,O1)

not just SUM instead of SUMPRODUCT. I think you have made this point to me
before, I will try and do better in future <bg>

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I apologize for the misspelling, but I don't think that I've ever
addressed
> you by your last name before.
> Considering I don't misspell Debra's last name, there's really no excuse
for
> getting yours wrong.
>
[quoted text clipped - 16 lines]
> > -
> > > Please keep all correspondence within the NewsGroup, so all may
benefit
> !
>
> --------------------------------------------------------------------------

> > -
> > > > =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}),
> > ">0"))

in
> > > > message news:FeedndY3Vb_ZwLDYRVnyvg@bt.com...
> > > > > Basically I have 13 cells spread out over a spreadsheet that
> normally
> > > have
> > > > > zero values. What I want to do is count the number of them that
have
> > > > > non-zero (actually will be greater than zero) values and return
that
> > > value
> > > > > in another cell. What I was thinking of was using COUNTIF, but
that
> > only
> > > > > seems to work for a contiguous range of cells. =COUNTIF (A1:H1,
[quoted text clipped - 11 lines]
> > > > >
> > > > > Marcus
Reply to this Message
Bob Phillips - 12 Oct 2006 18:25 GMT
Debra's name truly is of Scottish origin (I even think Debra has a touch if
the inge about her <g>), and is shared with the eminent, revered Kenny
Dalgleish.

Actually, I can't think of any other way to spell that name.

And as I said, my name suffers from being a household name to many people,
but spelt differently, which is why most think mine is spelt with one l.

On a lighter note, someone one asked me how I spelt my name, so I said two
L's, and they preceded to spell it Fillips (no helping some!").

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> You're right Bob, we did have an exchange on this very subject of Sum() and
> Sumproduct(), and that's why I attempted a frivolous comment as a reminder,
[quoted text clipped - 56 lines]
>
> --------------------------------------------------------------------------

> > > -
> > > > > =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}),
[quoted text clipped - 27 lines]
> > > > > >
> > > > > > Marcus
Reply to this Message
Debra Dalgleish - 12 Oct 2006 23:06 GMT
Hey! How did I get dragged into this? <g> And I'm not sure what inge is,
so I'll assume it's not an insult.

But since I'm involved now, I think your name may suffer from
proportional font-itis. It's hard to distinguish the letters when the
four narrow ones -- illi -- are squished together.

> Debra's name truly is of Scottish origin (I even think Debra has a touch if
> the inge about her <g>), and is shared with the eminent, revered Kenny
[quoted text clipped - 7 lines]
> On a lighter note, someone one asked me how I spelt my name, so I said two
> L's, and they preceded to spell it Fillips (no helping some!").

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message
Bob Phillips - 13 Oct 2006 00:34 GMT
Inge is vernacular over here for ginger, a  common trait of the Celts.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hey! How did I get dragged into this? <g> And I'm not sure what inge is,
> so I'll assume it's not an insult.
[quoted text clipped - 14 lines]
> > On a lighter note, someone one asked me how I spelt my name, so I said two
> > L's, and they preceded to spell it Fillips (no helping some!").
Reply to this Message
Pete_UK - 13 Oct 2006 01:37 GMT
Strange how some threads seem to wander way off-topic - I expect Epinn
will chastise you for not starting a new thread !! <bg>

Pete

> Inge is vernacular over here for ginger, a  common trait of the Celts.
>
[quoted text clipped - 31 lines]
> > Contextures
> > http://www.contextures.com/tiptech.html
Reply to this Message
Bob Phillips - 13 Oct 2006 09:21 GMT
LOL!

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Strange how some threads seem to wander way off-topic - I expect Epinn
> will chastise you for not starting a new thread !! <bg>
[quoted text clipped - 36 lines]
> > > Contextures
> > > http://www.contextures.com/tiptech.html
Reply to this Message
Harlan Grove - 12 Oct 2006 18:40 GMT
Bob Phillips wrote...
>=SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))

Or use just two nonvolatile function calls.

=INDEX(FREQUENCY((A1,C1,F1,H1,K1,L1,O1),0),2)
Reply to this Message
Marcus Fox - 12 Oct 2006 20:24 GMT
> =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))

Thanks for the advice. However, using this formula, I am having a problem
replicating it (downwards for about 500 rows, as A1 still stays as A1 and is
not changed to A2, C1 as C1 not C2, etc.

Marcus
Reply to this Message
Bob Phillips - 12 Oct 2006 22:10 GMT
Use Harlan's formula, that is copyable.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> > =SUMPRODUCT(COUNTIF(INDIRECT({"A1","C1","H1","F1","K1","L1","O1"}), ">0"))
>
[quoted text clipped - 3 lines]
>
> Marcus
Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.