COUNTIF?

M

Marcus Fox

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Ragdyer

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>
 
B

Bob Phillips

And how does SUM COUNT the cells with a value >0?

And the name is Phillips!

Bob Phillips
 
R

Ragdyer

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"))
 
B

Bob Phillips

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>

--
HTH

Bob Phillips

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

RagDyeR

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

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>

--
HTH

Bob Phillips

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

Bob Phillips

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!").

--
HTH

Bob Phillips

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

RagDyeR said:
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>
--

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>

--
HTH

Bob Phillips

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

Ragdyer said:
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"))

--
Regards,

RD
!
--------------------------------------------------------------------------
-


-------------------------------------------------------------------------- benefit

--------------------------------------------------------------------------
 
H

Harlan Grove

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

Marcus Fox

Bob Phillips said:
=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
 
B

Bob Phillips

Use Harlan's formula, that is copyable.

--
HTH

Bob Phillips

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

Debra Dalgleish

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.
 
B

Bob Phillips

Inge is vernacular over here for ginger, a common trait of the Celts.

--
HTH

Bob Phillips

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

Pete_UK

Strange how some threads seem to wander way off-topic - I expect Epinn
will chastise you for not starting a new thread !! <bg>

Pete
 
B

Bob Phillips

LOL!

--
HTH

Bob Phillips

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

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

Top