Any way to get a SUMPRODUCT formula for non-contiguous cells?
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Any way to get a SUMPRODUCT formula for non-contiguous cells?

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Any way to get a SUMPRODUCT formula for non-contiguous cells?

    What i would like is Sumproduct of array1= A1,A3. Array2 = B1, B3.

    =SUMPRODUCT((A1,A3),(B1,B3)) gives #VALUE . If you try to use {}, SUMPRODUCT will only accept constant numbers, not cell references.

    When i type the formula with the (), the formula tip seems to suggest it is working. For instance, when i type
    "=SUMPRODUCT((A1,A3)," after typing the comma it highlights array2, suggesting to me it accepted the expression "(A1,A3)" as array1...yet still final result is #VALUE .

    Is it possible?

    Thanks!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    No, I don't think you can use SUMPRODUCT on non-contiguous ranges).
    But you could use a number of other formulas for your simple example, like:
    =SUMPRODUCT(A1,B1)+SUMPRODUCT(A3,B3)
    =SUM(A1*B1,A3*B3)
    =(A1*B1)+(A3*B3)


    I have a feeling that you are oversimplying the real problem. If you tell us what the real ranges look like, and if there is any sort of pattern, we may be able to provide alternate solutions for you.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Maybe something like this...

    =SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))

    M.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Maybe something like this...

    =SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))
    EDIT: I was getting unexpected results using your formula initially, but then I noticed that you are using C1 and C3, and not B1 and B3 as in the original question.
    So if you were tailoring your response to his conditions, it should be:
    Code:
    =SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},B1,B3))
    Last edited by Joe4; Mar 26th, 2019 at 02:44 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Quote Originally Posted by Joe4 View Post
    Marcelo,
    I tried that, and it seemed to double my expected result.

    For example, if A1=2, B1=3, A3=3, B3=5, the expected result is (2*4) + (3*5) = 23.
    But your formula is returning 46 for me.
    Joe,

    It worked for me - returned 23 as expected.

    M.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Marcelo,
    Go back and see the edit I made to my response a few minutes ago.
    It looks like you were using different cell addresses than were posted in the original question, and I missed you changed that.
    Just wanted to make sure that the OP sees that you did that, or they might have the same issue.
    Last edited by Joe4; Mar 26th, 2019 at 02:48 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?


    A
    B
    C
    D
    E
    1
    2
    4
    Result
    2
    100
    200
    23
    3
    3
    5


    Formula in E2
    =SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))

    M.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Quote Originally Posted by Joe4 View Post
    Marcelo,
    Go back and see the edit I made to my response a few minutes ago.
    It looks like you were using different cell addresses than were posted in the original question, and I missed you changed that.
    Just wanted to make sure that the OP sees that you did that, or they might have the same issue.
    Oh, by accident i used C1 and C3 in the second array rather than B1 and B3.
    But the formula works.

    M.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Oh, by accident i used C1 and C3 in the second array rather than B1 and B3.
    But the formula works.
    Yep, it works, after you account for that.
    Sorry if that wasn't clear in my edit.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

    Another way

    =SUMPRODUCT(A1:A3,C1:C3, --(MOD(ROW(A1:A3),2)=1))

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •