SUMIF multiple columns - Page 2

Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: SUMIF multiple columns

  1. #11
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,804
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMIF multiple columns

     
    Try this

    Formula in B10 copied down
    =SUMIF($B$1:$E$1,$B$9,INDEX($B$2:$E$6,MATCH($A10,$A$2:$A$6,0),0))

    M.

  2. #12
    New Member
    Join Date
    Dec 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF multiple columns

    Yes, thank you. I thought it was going to be an Index/Match. I for the life of me cannot master the Index/Match, I get the vlookup and the hlookup, trouble with the Index/Match.

  3. #13
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,804
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMIF multiple columns

    Quote Originally Posted by Rugman67 View Post
    Yes, thank you. I thought it was going to be an Index/Match. I for the life of me cannot master the Index/Match, I get the vlookup and the hlookup, trouble with the Index/Match.

    See
    http://www.contextures.com/xlFunctions03.html

    M.

  4. #14
    New Member
    Join Date
    Apr 2016
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF multiple columns

    Quote Originally Posted by Marcelo Branco View Post

    I'm hoping this is the place for some help on a similar problem. I would like to sumif data from multiple columns but to build on this example where there are repeats in the rows (so column a could be a,b,c,a,a,d,e). Also rather than matching the column title I want to make the number of columns vary based on the current month (so include columns B:H if it is July or month 7 and include columns B:M if it is December or Month 12).

    So the data may look like below.

    I've tried a couple approaches to achieve this.

    First I adapted the formula in this thread but realise the match will only return the first instance of "material 1"
    =SUM(INDEX($B$2:$H$5,MATCH(A10,$A$2:$A$5,0),0))

    Second I tried achieving this with a combination of offset and sumproduct. The formula I have though covers the full 12 months and I don't know how to adapt the index (without using indirect) to only sum months up until specified current month.
    =SUMPRODUCT(($A$2:$A$5=$A10)*(OFFSET($B$2:$M$5,0,0,,)))

    Greatly apprecaite any views.


    A B C D E F G H I J K L M
    1 jan feb mar apr may jun jul aug sep oct nov dec
    2 Material 2 22 11 69 131 236 2 6
    3 Service 5 5 23 31 53 3 39
    4 Material 2 22 11 69 131 236 2 6
    5 Service 3 3 5 302 514 689 3 29
    6
    7
    8
    9 Current Month 7
    10 Material 2 954
    11 Service
    12 Service 3


  5. #15
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,804
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMIF multiple columns

    Maybe this

    B10 copied down
    =SUMPRODUCT((COLUMN($B1:$M1)-COLUMN($B1)+1<=B$9)*(A$2:A$5=A10)*B$2:M$5)

    M.

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,463
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF multiple columns

      
    Equally, in B10 control+shift+enter, not just enter, and copy down:

    =SUM(IF($A$2:$A$5=$A10,OFFSET($B$2:$M$5,0,0,,B$9)))

    A formula blank or text value would not affect this adversely.
    Assuming too much and qualifying too much are two faces of the same problem.

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
  •  

 

 
DMCA.com