countif and sum for arrays

[freekrill]

I think that I need to use array formulas for the following tw
problems, but I can't get them to work. Any assistance would be greatl
appreciated.

1) counting the number of "numbers" in a row. At the moment, I have

{=countif(A1:G1,"isnumber(A1:G1)=TRUE")}

2) summing a row of numbers and blanks, where some of the numbers ar
text entries (eg, +3, -5) which I want to read as numbers.

At the moment, I am using =value(a1) + value(b1) + value(c1) +..., bu
I thought that there must be an easier way.

Thanks
fre

 

[Harlan Grove]

1) counting the number of "numbers" in a row. At the moment, I have

{=countif(A1:G1,"isnumber(A1:G1)=TRUE")}

No! Use =COUNT(A1:G1)

2) summing a row of numbers and blanks, where some of the numbers are
text entries (eg, +3, -5) which I want to read as numbers.

=SUMPRODUCT(--A1:G1)

 

[Andrew]

For the first problem, why not enter =IF(ISNUMBER(A1),1,0) in cell A
and drag across to G2, then in H2 enter =SUM(A2:G2).

I'm not sure what you want with problem 2. If the first character i
text and then the rest is a number, tr
=VALUE(SUBSTITUTE(A1,LEFT(A1,1),"")

 

[Ron H]

For the first problem try
=COUNT((a1:g1))*(ISNUMBER(a1:g1))
entered as an array formula (Ctrl:Shft:Enter)


Ron Hekier

 

[Andrew]

Ron,

I tried your formula and got 0. Maybe I didn't do it right.

I was just playing around now, to count numeric entries only this seem
to work =SUMPRODUCT(--(ISNUMBER(A1:G1))). It also does not see
necessary to be entered as an array formula

 

[Andrew]

Alternatives are

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

and

=SUMPRODUCT((ISNUMBER(A1:G1))*1

 

[Peo Sjoblom]

to sum text numbers and real numbers with a range that can hold real text
values

=SUM(IF(ISNUMBER(--A1:G1),--A1:G1))

entered with ctrl + shift & enter

otherwise go with Harlan's solution

--
Regards,

Peo Sjoblom

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

 

[Ron H]

Make sure you enter my formula as an array formula using Ctrl_Shft_Ente
otherwise you get 0. I've tried it twice and works fine for me.

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

Your solution with SUMPRODUCT works well.

 

[Peo Sjoblom]

Although it works it's really wasteful since Harlan's

=COUNT(A1:G1)

will work.

--
Regards,

Peo Sjoblom

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

 

[Andrew]

Hi Ron,

I tried again and this time your formula worked perfectly. Pleas
accept my apologies. I wonder what the problem was the first time?

I like your formula too. It's quite easy to see what it does by th
function names alone

 

[Peo Sjoblom]

Why do you like it? Can you show me a reason to use it compared to using?

=COUNT(A1:G1)

from help

"Counts the number of cells that contain numbers and also numbers within the
list of arguments. Use COUNT to get the number of entries in a number field
that's in a range or array of numbers."
Now as an experiment put either a textnumber '1 or text "a" in A1 and see
what happens?

Not only it is wasteful it will also return wrong result


--
Regards,


Peo Sjoblom

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

 

[QTE]

Hi Ron,

If the value of the first cell in the range is a non-numeric value
i.e. AA100, 10, 20, 30, AB200, AB300, 70 etc; I think your formul
could give an incorrect / false return value of zero numeric values
even when the formula is entered as an array formula(Ctrl_Shft_Enter
and there are numeric values in the adjacent cells within the specifie
range. You may need to use the (+) OR operator rather than the * (AND
operator you have used.

Please try your formula with a non-numeric value in the first cell o
the range.

Kind regards
QTE

 

[Ragdyer]

It's frustrating, isn't it Peo?
Some people insist on bypassing the obvious *and* the simplest, just because
they didn't think of it first!
--
Regards,

RD

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

Why do you like it? Can you show me a reason to use it compared to using?

=COUNT(A1:G1)

from help

"Counts the number of cells that contain numbers and also numbers within the
list of arguments. Use COUNT to get the number of entries in a number field
that's in a range or array of numbers."
Now as an experiment put either a textnumber '1 or text "a" in A1 and see
what happens?

Not only it is wasteful it will also return wrong result


--
Regards,


Peo Sjoblom

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

 

[Peo Sjoblom]

Hi RD,

To be fair, I just went to that bloody forum and guess what, neither
Harlan's nor mine posts were there!
Maybe I'm blacklisted? <g>

--
Regards,

Peo Sjoblom

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

It's frustrating, isn't it Peo?
Some people insist on bypassing the obvious *and* the simplest, just because
they didn't think of it first!
--
Regards,

RD

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

 

[Ron H]

QTE,
You are right. Why is it that the value is returned 0 if the firs
cell is non-numeric?

The OR operator works if the first cell is non-numeric, but returns a
incorrect value if the first value is numeric.

 

[Harlan Grove]

To be fair, I just went to that bloody forum and guess what, neither
Harlan's nor mine posts were there!
Maybe I'm blacklisted? <g>

....

Dunno 'bout you, but I can only hope . . .

 

[QTE]

Hi Ron,

I am not sure why the formula appears to work vice versa with the AND
Or operators. Perhaps a combination of both is needed in the formula.
Not tried it as yet. The Sumproduct formula that Andrew gave as a
alternative maybe the way to go, as either formula works whether th
first cell is non-numeric or numeric.

*QTE,
You are right. Why is it that the value is returned 0 if the firs
cell is non-numeric?

The OR operator works if the first cell is non-numeric, but return
an incorrect value if the first value is numeric. *

Kind Regards,
QT

 

[Ragdyer]

That might explain some "funny" answers, or lack there of, of some past
replies.

My comment was *also* not being displayed.

I sent "somebody" over there a message on your observation.

 

[Peo Sjoblom]

Maybe you could post there that they should use COUNT since they are still
discussing
using SUMPRODUCT.. <g>

--
Regards,

Peo Sjoblom

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

 

[freekrill]

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?

fre