Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Summing multiple criteria across rows and columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Summing multiple criteria across rows and columns

    I need to be able to solve the following problem without arrays or sum product solution. Would be grateful to anyone that can help.

    If have a set of data with columns:
    1 3 5 2 1 5 2 3

    And rows:
    Dog
    Cat
    Fish
    Dog
    Dog
    Cat

    and there is data in each cell in this matrix.

    I need to sum the values where the column and row values match (i.e. sum all values where column = 1 and row = dog).

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    hi - welcome to the board.

    Imagine your data is in b1:h8:

    =SUM(IF(C1:H1=1,IF(B2:B8="dog",C2:H8)))

    ..entered with control + shift + enter, not just enter.
    Two plus two equals five for large values of two.

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    Sorry - I have just seen your 'no arrays or sumproducts' restriction.

    In which case:

    Do it long hand (i.e. once for each row in the table) then add up the results.
    Two plus two equals five for large values of two.

  4. #4
    New Member
    Join Date
    Sep 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    Thank you for the reply, PaddyD. However, I don't want to do an array - the performance is poor on large data sets. Also, I want to have something that is dynamic. I have one formula that uses sumif, offset and match, but only aggregates multiple row headers.

  5. #5
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    "..the performance is poor on large data sets. Also, I want to have something that is dynamic."

    Provide more details about the real world scenario you are working with.
    Two plus two equals five for large values of two.

  6. #6
    New Member
    Join Date
    Sep 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    Animal Feb March Feb
    cats 8 23 1
    dogs 9 12 2
    cats 2 5 6
    birds 2 4 4
    dogs 5 8 3


    SUMIFS(OFFSET($B$3:$B$7,0,MATCH($C$2:$E$2,C$9,0),),$B$3:$B$7,$B10) This formula will only aggregate row headers

    SUMPRODUCT(($B$3:$B$7=$B12)*($C$2:$E$2=C$9)*($C$3:$E$7)) This formula woks but I do not want to us an array


    Any suggestion?


    Thanks for your help!

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    Is this what you want?

    =SUMIF(B3:B7,B12,INDEX(C3:E7,0,MATCH(C9,C2:E2,FALSE)))
    Microsoft MVP - Excel

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    Quote Originally Posted by Andrew Poulsom View Post
    Is this what you want?

    =SUMIF(B3:B7,B12,INDEX(C3:E7,0,MATCH(C9,C2:E2,FALSE)))

    Unfortunately, no. That formula is similar to my first example. It only sums the animal that matches the first matched period (feb)- it does not sum the values that intersect both Feb periods like the array example does. Any other suggestions?

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    How about?

    =SUM(INDEX((C3:E7)*(B3:B7=B12)*(C2:E2=C9),))
    Microsoft MVP - Excel

  10. #10
    New Member
    Join Date
    Sep 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Summing multiple criteria across rows and columns

    I assume that is an array? I want to avoid using arrays due to performance. Any other suggestions?

Some videos you may like

User Tag List

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
  •