# SUMPRODUCT (I believe??)

Discussion in 'Microsoft Excel Programming' started by Guest, Sep 14, 2006.

1. ### GuestGuest

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:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!

Thank you,

FLKulchar

Guest, Sep 14, 2006

2. ### Franz VergaGuest

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:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
>
> Thank you,
>
> FLKulchar

Hi Lawrence,

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

=SUM((A1:E5>0)*(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.

Ciao

Franz Verga from Italy

Franz Verga, Sep 14, 2006

3. ### GuestGuest

=SUMPRODUCT(A1:E5,--(A1:E5>0),--(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:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
>
> Thank you,
>
> FLKulchar

Guest, Sep 14, 2006
4. ### Bob PhillipsGuest

=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 news:...
> 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:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
>
>
> Thank you,
>
> FLKulchar

Bob Phillips, Sep 14, 2006
5. ### Bob PhillipsGuest

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

--
HTH

Bob Phillips

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

"Franz Verga" <> wrote in message
news:evQjeJ\$...
> 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:E5>0,A1:E5<10),A1:E5)...BUT NOT QUITE SO!!!!!!!!!!!
> >
> >
> > Thank you,
> >
> > FLKulchar

>
>
> Hi Lawrence,
>
> try with this formula, array entered, i.e. pressing Ctrl + Shift + Enter,
>
>
> =SUM((A1:E5>0)*(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.
>