SUMIF multiple columns

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

Thread: SUMIF multiple columns

  1. #1
    New Member
    Join Date
    Jul 2004
    Location
    St. Louis
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIF multiple columns

     
    I'm trying to sumif for a range. Pretend below is an exceil file with A1=1, and D4=A. I wrote "=SUMIF(D1:D4,"A",A1:C4)". The answer would be "7", or 1+2+2 in the first "A" row plus 1+0+1 in the second "A" row. It worked for a while, but now it won't. The problem is A1:C4, it seems the SUMIF command wants this to be A1:A4. I could make a big sumif command "=SUMIF(D1:D4,"A",A1:A4)+SUMIF(D1:D4,"A",B1:B4)+SUMIF(D1:D4,"A",C1:C4)", but would rather be clever.

    1, 2, 2, A
    2, 2, 1, B
    1, 1, 1, C
    1, 0, 1, A

    Thanks,

    Joe

  2. #2
    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

    Hi,

    Maybe

    =SUMPRODUCT($A$1:$C$4*($D$1:$D$4="A"))

    HTH

    M.

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

    Default Re: SUMIF multiple columns

    Quote Originally Posted by joe-carter View Post
    I'm trying to sumif for a range. Pretend below is an exceil file with A1=1, and D4=A. I wrote "=SUMIF(D1:D4,"A",A1:C4)". The answer would be "7", or 1+2+2 in the first "A" row plus 1+0+1 in the second "A" row. It worked for a while, but now it won't. The problem is A1:C4, it seems the SUMIF command wants this to be A1:A4. I could make a big sumif command "=SUMIF(D1:D4,"A",A1:A4)+SUMIF(D1:D4,"A",B1:B4)+SUMIF(D1:D4,"A",C1:C4)", but would rather be clever.

    1, 2, 2, A
    2, 2, 1, B
    1, 1, 1, C
    1, 0, 1, A

    Thanks,

    Joe
    Switch to...

    Control+shift+enter, not just enter:

    =SUM(IF($D$1:$D$4="A",$A$1:$C$4))

    Note to Marcelo. The star syntax to over vector/matrix multiplications will run into problems if the matrix reference houses blanks or other text.
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Jul 2004
    Location
    St. Louis
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF multiple columns

    =SUMPRODUCT($C$3:$F$65,($N$3:$N$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.

    I actually have (8) columns to add in this matter. I guess the better question is, how can I be more concise with the following formula:
    =SUMIF($N$3:$N$65,K67,$C$3:$C$65)+SUMIF($N$3:$N$65,K67,$D$3:$D$65)
    +SUMIF($N$3:$N$65,K67,$E$3:$E$65)+SUMIF($N$3:$N$65,K67,$F$3:$F$65)
    +SUMIF($N$3:$N$65,K67,$V$3:$V$65)+SUMIF($N$3:$N$65,K67,$W$3:$W$65)
    +SUMIF($N$3:$N$65,K67,$X$3:$X$65)+SUMIF($N$3:$N$65,K67,$Y$3:$Y$65).

    Notice my (8) columns of C, D, E, F, then V, W, X, Y. Since the criteria is the same (wherever $N$3:$N$65 equals K67), I should be able to create a more concise formula.

    joe

  5. #5
    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

    =SUMPRODUCT($C$3:$F$65,($N$3:$N$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.
    Try this

    =SUMPRODUCT($C$3:$F$65*($N$3:$N$65="A"))

    M.

  6. #6
    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

    Aladin
    Note to Marcelo. The star syntax to over vector/matrix multiplications will run into problems if the matrix reference houses blanks or other text.
    You are right, as usual. Tks for for the advice.
    (i had not seen it when i made my last post)

    M.

  7. #7
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMIF multiple columns

    Quote Originally Posted by joe-carter View Post
    =SUMPRODUCT($C$3:$F$65,($N$3:$N$65="A")) is what I typed in and it worked for a moment, but now it won't, just like the SUMIF.

    I actually have (8) columns to add in this matter. I guess the better question is, how can I be more concise with the following formula:
    =SUMIF($N$3:$N$65,K67,$C$3:$C$65)+SUMIF($N$3:$N$65,K67,$D$3:$D$65)
    +SUMIF($N$3:$N$65,K67,$E$3:$E$65)+SUMIF($N$3:$N$65,K67,$F$3:$F$65)
    +SUMIF($N$3:$N$65,K67,$V$3:$V$65)+SUMIF($N$3:$N$65,K67,$W$3:$W$65)
    +SUMIF($N$3:$N$65,K67,$X$3:$X$65)+SUMIF($N$3:$N$65,K67,$Y$3:$Y$65).

    Notice my (8) columns of C, D, E, F, then V, W, X, Y. Since the criteria is the same (wherever $N$3:$N$65 equals K67), I should be able to create a more concise formula.

    joe
    Try this array formula**:

    =SUM(IF(N3:N65=K67,C3:F65),IF(N3:N65=K67,V3:Y65))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  8. #8
    New Member
    Join Date
    Jul 2004
    Location
    St. Louis
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF multiple columns

    both worked,
    {=SUM(IF($N$3:$N$65=K67,$C$3:$F$65))+SUM(IF($N$3:$N$65=K67,$V$3:$Y$65))}
    AND
    {=SUM(IF($N$3:$N$65=K67,$C$3:$F$65),IF(N3:N65=K67,$V$3:$Y$65))

    I went with the second one. It will hopefully get me thinking about using array formulas more.

    Thanks!

  9. #9
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SUMIF multiple columns

    Quote Originally Posted by joe-carter View Post
    both worked,
    {=SUM(IF($N$3:$N$65=K67,$C$3:$F$65))+SUM(IF($N$3:$N$65=K67,$V$3:$Y$65))}
    AND
    {=SUM(IF($N$3:$N$65=K67,$C$3:$F$65),IF(N3:N65=K67,$V$3:$Y$65))

    I went with the second one. It will hopefully get me thinking about using array formulas more.

    Thanks!
    You're welcome. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

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

    Default Re: SUMIF multiple columns

      


    In cell B10 - B12 I am trying to add the data from the table above A1:E10. In other words in cell B10 I am trying to sum the data in cells B2 to E2, with the criteria of "Jan" and "a". In all the videos on YouTube I only saw sumifs being used for columns with different criteria, here we have the same criteria. I know you could us a simple "sum" but it is very manual especially if the data is on a different worksheet and it span 100+ columns. I hope the question makes sense. Thank you.

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