Results 1 to 9 of 9

Thread: SUMIFS multiple criteria from same column

  1. #1
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14

    SUMIFS multiple criteria from same column

    I have SUMIFS formula where I want to use multiple criteria from the same column apart from other columns.

    I have attached the sample file.

    Pls assist.
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    1.

    =SUMPRODUCT(SUMIFS($C$2:$C$7,$A$2:$A$7,INDEX(A14:B 14,0,0),$B$2:$B$7,$C$14))

    2.

    =SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,{"Micl","Pirt"},$B $2:$B$7,$C$14))

    3.

    =SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,A14:B14,0)),--(B2:B7=C14),C2:C7)

    HTH

  3. #3
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    thanks, but i have date also in the criteria in cell C9.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    OK.

    1.

    =SUMPRODUCT(SUMIFS(INDEX($C$2:$E$7,0,MATCH(C9,C1:E 1,0)),$A$2:$A$7,INDEX(A14:B14,0,0),$B$2:$B$7,$C$14 ))

    2.

    =SUM(SUMIFS(INDEX($C$2:$E$7,0,MATCH(C9,C1:E1,0)),$ A$2:$A$7,{"Micl","Pirt"},$B$2:$B$7,$C$14))

    3.

    =SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,A14:B14,0)),--(B2:B7=C14),INDEX($C$2:$E$7,0,MATCH(C9,C1:E1,0)))

    HTH

  5. #5
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Thanks, its working...
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Glad I could help

  7. #7
    Junior Member
    Join Date
    May 2012
    Posts
    2
    Rep Power
    0
    Good Afternoon - I apologize in advance for responding to this thread, but am new and can't figure out where to post my own thread? I have a similar problem but not sure it's the same solution. How do I attach my workbook?

    Thanks in advance.

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by MarkJohn View Post
    ...and can't figure out where to post my own thread? How do I attach my workbook?
    First, click the Forum button located under the EXCELFOX.com logo. Next, locate the "Help Section" forum and click the sub-forum link labeled "Excel Help". This will take you into the Excel Help sub-forum where you can read other threads; however, located towards the top is a large button labeled "+Post New Thread"... click that button to take you to a form that you can type your question into. Located below the message panel where you type your question is another panel titled "Additional Options"... inside its panel is a button labeled "Manage Attachments"... click that button and one of its own options allows you to "Upload Files from your Computer".

  9. #9
    Junior Member
    Join Date
    Jun 2012
    Posts
    10
    Rep Power
    0

    SUMIFS Multiple Criteria in Single Cloumn

    Quote Originally Posted by Admin View Post
    Hi,

    1.

    =SUMPRODUCT(SUMIFS($C$2:$C$7,$A$2:$A$7,INDEX(A14:B 14,0,0),$B$2:$B$7,$C$14))

    2.

    =SUM(SUMIFS($C$2:$C$7,$A$2:$A$7,{"Micl","Pirt"},$B $2:$B$7,$C$14))

    3.

    =SUMPRODUCT(--ISNUMBER(MATCH(A2:A7,A14:B14,0)),--(B2:B7=C14),C2:C7)

    HTH


    Hi

    I also faced same problem but above formulas solved my problem. Thanks. But I have query. While using formula no. 2, I tried to give cell reference instead of placing values in double quotation like {"Micl", "Pirt"} but it didn't work. Please can you tell me how SUM function worked in this formula.

    Although formula no. 1 solved my problem and I selected range instead of placing values in curly bracket and double quotations. But I am unable to interpretate how SUMPRODUCT is working here. Please tell me how SUMPRODUCT is working here.

    Thanks

Similar Threads

  1. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  2. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  3. Replies: 7
    Last Post: 05-15-2013, 07:55 AM
  4. Replies: 4
    Last Post: 08-11-2012, 10:50 PM
  5. SUM Value With Multiple Criteria In Same Column
    By MarkJohn in forum Excel Help
    Replies: 2
    Last Post: 05-18-2012, 07:52 AM

Tags for this Thread

Posting Permissions

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