countif and sum for arrays

  • Thread starter Thread starter freekrill
  • Start date Start date
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
 
terre08 > said:
Regards,

Peo Sjoblom
Click to expand...
....

Cheater, using an alternative personna. Pretty soon you'll start amassing
huge number of Passport IDs like JE claims to have done.
 
freekrill > said:
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)
Click to expand...
....

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.
 
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 *
 
Harlan Grove said:
...

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

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)
 
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
 
Andrew > said:
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)
Click to expand...
....

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