countif and sum for arrays

[terre08]

Why not use?


=COUNT(A1:G1)




Regards,

Peo Sjoblom


*Thanks for everyone's input.

I get a different result depending on whether I use:

{=COUNT((a1:g1))*(ISNUMBER(a1:g1))}

or

=SUMPRODUCT((ISNUMBER(A1:G1))+0)

The first appears to count blanks as 0 (ie. a number)

Is this what you have found?

free

 

[Harlan Grove]

Regards,

Peo Sjoblom

....

Cheater, using an alternative personna. Pretty soon you'll start amassing
huge number of Passport IDs like JE claims to have done.

 

[Harlan Grove]

I get a different result depending on whether I use:

{=COUNT((a1:g1))*(ISNUMBER(a1:g1))}

or

=SUMPRODUCT((ISNUMBER(A1:G1))+0)

The first appears to count blanks as 0 (ie. a number)

....

Correct. In arithmetic expressions, blank cells are converted to numeric
zeros, and 0 (from A1:G1) times 0 (from ISNUMBER(A1:G1)) is still 0, and is
a number, so is included in COUNT's result.

The second formula avoids this, but it could and should be replaced by
=COUNT(A1:G1). There *are* times when simpler is better! Whether anyone
using excelforum will ever see this is open to some doubt.

 

[RagDyeR]

It must have really "got your goat", for you to post in that "ExcelForum",
just to show them a truly simple solution.

I see that you're a *junior* member over "there" Peo.

When do you "graduate"?<bg>
--

Regards,

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





Why not use?


=COUNT(A1:G1)




Regards,

Peo Sjoblom


*Thanks for everyone's input.

I get a different result depending on whether I use:

{=COUNT((a1:g1))*(ISNUMBER(a1:g1))}

or

=SUMPRODUCT((ISNUMBER(A1:G1))+0)

The first appears to count blanks as 0 (ie. a number)

Is this what you have found?

free *

 

[Peo Sjoblom]

...

Cheater, using an alternative personna. Pretty soon you'll start amassing
huge number of Passport IDs like JE claims to have done.

It won't happen again, I just couldn't take the discussion that was going
on <g>


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Andrew]

Ha ha! Thanks Mr. Sjoblom for showing us the easiest way how!

For anyone who has followed this thread, it's possible to count *text
also with =COUNTA(A1:G1)-COUNT(A1:G1) or =SUMPRODUCT(--(ISTEXT(A1:G1))
(Also =SUMPRODUCT((ISTEXT(A1:G1))*1) o
=SUMPRODUCT((ISTEXT(A1:G1))+0)

Blanks are not counted

 

[Harlan Grove]

For anyone who has followed this thread, it's possible to count *text*
also with =COUNTA(A1:G1)-COUNT(A1:G1) or =SUMPRODUCT(--(ISTEXT(A1:G1)))
(Also =SUMPRODUCT((ISTEXT(A1:G1))*1) or
=SUMPRODUCT((ISTEXT(A1:G1))+0)

....

For text, the SUMPRODUCT formulas are more robust. COUNTA-COUNT would
include any error values or boolean (True/False) values in its result.