SUMPRODUCT (I believe??)

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

  1. Guest

    Guest Guest

    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!!!!!!!!!!!

    PLEASE ADVISE.

    Thank you,

    FLKulchar
     
    Guest, Sep 14, 2006
    #1
    1. Advertisements

  2. Guest

    Franz Verga Guest

    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!!!!!!!!!!!
    >
    > 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: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.

    Thanks in advance for your feedback.

    Ciao

    Franz Verga from Italy
     
    Franz Verga, Sep 14, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    =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!!!!!!!!!!!
    >
    > PLEASE ADVISE.
    >
    > Thank you,
    >
    > FLKulchar
     
    Guest, Sep 14, 2006
    #3
  4. Guest

    Bob Phillips Guest

    =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!!!!!!!!!!!
    >
    > PLEASE ADVISE.
    >
    > Thank you,
    >
    > FLKulchar
     
    Bob Phillips, Sep 14, 2006
    #4
  5. Guest

    Bob Phillips Guest

    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!!!!!!!!!!!
    > >
    > > 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: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.
    >
    > Thanks in advance for your feedback.
    >
    > Ciao
    >
    > Franz Verga from Italy
    >
    >
     
    Bob Phillips, Sep 14, 2006
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. RobcPettit

    Average/Sumproduct Array

    RobcPettit, Jul 9, 2003, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    686
    RobcPettit
    Jul 9, 2003
  2. Guest

    SUMPRODUCT - NO SUMPRODUCT!

    Guest, Nov 23, 2005, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    204
    Ron Coderre
    Nov 23, 2005
  3. Guest

    I can't believe how complicated this is...

    Guest, Jul 27, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    797
    Guest
    Jul 27, 2006
  4. Replies:
    12
    Views:
    236
    Dave Peterson
    Nov 3, 2009
  5. Majken Bilslev-Jensen

    Sumproduct instead of SumifS in VBA (application.sumproduct)

    Majken Bilslev-Jensen, Dec 30, 2010, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    3,439
    joeu2004
    Dec 30, 2010
Loading...

Share This Page