I have an array of numbers and/or text values in cells A1 through E5, such as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8 =
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E50,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar

F. Lawrence Kulchar wrote:
I have an array of numbers and/or text values in cells A1 through E5,
such as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE
ALL OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3
+ 8 =
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E50,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar


Hi Lawrence,

try with this formula, array entered, i.e. pressing Ctrl + Shift + Enter,
instead of just Enter.


=SUM((A1:E50)*(A1:E5<10)*IF(ISERROR(VALUE(A1:E5)) ,0,A1:E5))

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

or even =SUMPRODUCT((A1:E50)*(A1:E5<10),A1:E5)

--
HTH

Bob Phillips

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

"Franz Verga" wrote in message
...
F. Lawrence Kulchar wrote:
I have an array of numbers and/or text values in cells A1 through E5,
such as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE
ALL OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3
+ 8 =
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E50,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar


Hi Lawrence,

try with this formula, array entered, i.e. pressing Ctrl + Shift + Enter,
instead of just Enter.


=SUM((A1:E50)*(A1:E5<10)*IF(ISERROR(VALUE(A1:E5)) ,0,A1:E5))

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

=SUMPRODUCT(A1:E5,--(A1:E50),--(A1:E5<10))
regards,
Stefi


€žF. Lawrence Kulchar€� ezt Ã*rta:

I have an array of numbers and/or text values in cells A1 through E5, such as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8 =
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E50,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar

=SUMIF(A1:E5,"0")-SUMIF(A1:E5,"10")

--
HTH

Bob Phillips

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

"F. Lawrence Kulchar" wrote in
message ...
I have an array of numbers and/or text values in cells A1 through E5, such
as:

A B C D E

1 12 6 A Y -5
2 4 2 3 AB 9
3 GH -1 -6 0 17
4 A V R -9 3
5 2 1 6 3 8

AND I WISH TO ADD THE CELLS where the values are 0 < X < 10. IGNORE ALL
OTHER CELL VALUES.

THEREFORE, my answer will be: 6 + 4 + 2 + 3 + 9 + 3 + 2 + 1 + 6 + 3 + 8
=
47.

Please, how is this done...using the =SUMPRODUCT formula...

It is something such as:

=SUMPRODUCT(AND(A1:E50,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

PLEASE ADVISE.

Thank you,

FLKulchar