+ Reply to Thread
Results 1 to 16 of 16

SumIF with a list of criteria

  1. #1
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    155

    SumIF with a list of criteria

    Attached is a worksheet that is an example of what I am trying to accomplish. I want a formula for cell I4.

    Column A is a list of criteria numbers. I need a formula that says if I2 is seen in column D and I3 is seen in column C, and one of the criteria codes in column A is seen in column E, then I want to sum all the values in column F.

    I believe it is a sumproduct with an array or something of this nature.
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: SumIF with a list of criteria

    So as per your example the result should be 0(zero)?!
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SumIF with a list of criteria

    Maybe this

    =SUMPRODUCT((C2:C53=I3)*(D2:D53=I2)*(F2:F53))

    Or

    =SUMIFS(F2:F53,C2:C53,I3,D2:D53,I2)
    Last edited by AlKey; 09-17-2014 at 11:35 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SumIF with a list of criteria

    Maybe this?

    =SUMIFS(F:F,D:D,$I$2,C:C,$I$3)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: SumIF with a list of criteria

    with an pivot table.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: SumIF with a list of criteria

    Check the attached file..and tell if that is what you were trying to achieve..
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  7. #7
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: SumIF with a list of criteria

    None of these formulas work. If any of the criteria codes are seen in column E, I need the total values of them all that are coming from column F. For example, 28820 for Adam in the finance department is seen with a value of 2. That would be one of the values.

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SumIF with a list of criteria

    Quote Originally Posted by lorber123 View Post
    None of these formulas work. If any of the criteria codes are seen in column E, I need the total values of them all that are coming from column F. For example, 28820 for Adam in the finance department is seen with a value of 2. That would be one of the values.
    So, what is the expected answer should be?

  9. #9
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: SumIF with a list of criteria

    HI,

    Try this Array Function in i4
    =SUMIFS(F2:F53,C2:C53,I3,D2:D53,I2,E2:E53,INDEX(E2:E32,SMALL(IF((C2:C32=I3)*(D2:D32=I2),ROW(C2:C32)-ROW(C2),""),ROW(1:1))))

    Use Ctrl+Shift+Enter
    Punnam

  10. #10
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: SumIF with a list of criteria

    None of these formulas work. There has to be a sumifs function that can sum a column based on a list of criteria....

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SumIF with a list of criteria

    Quote Originally Posted by lorber123 View Post
    None of these formulas work. There has to be a sumifs function that can sum a column based on a list of criteria....
    You still didn't answer the question. What is the expected answer should be?

  12. #12
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: SumIF with a list of criteria

    Quote Originally Posted by lorber123 View Post
    None of these formulas work. If any of the criteria codes are seen in column E, I need the total values of them all that are coming from column F. For example, 28820 for Adam in the finance department is seen with a value of 2. That would be one of the values.
    ,

    As per this statement the answer is "2" am i correct ,if yes check formula in Post 9.

    Use Ctrl+Shift+enter after pasting the function yellow Colored Box .

    Punnam

  13. #13
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: SumIF with a list of criteria

    Quote Originally Posted by Punnam View Post
    ,

    As per this statement the answer is "2" am i correct ,if yes check formula in Post 9.

    Use Ctrl+Shift+enter after pasting the function yellow Colored Box .

    Punnam
    It think OP is looking also for criteria codes in Column A should match the once in column E. But OP still din't confirm this.

  14. #14
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: SumIF with a list of criteria

    I have attached the document and highlighted everything. Column A has the criteria codes. The only two criteria codes seen in column E that are next to the department of Finance and the Name Adam are 28820 in E2 and 27780 in E14.

    The answer would be 16.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: SumIF with a list of criteria

    Hi,

    Check this i am not sure
    =SUMPRODUCT((C2:C53=I3)*(D2:D53=I2)*(COUNTIF(A2:A53,E2:E53)>0)*(F2:F53))

    Punnam

  16. #16
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: SumIF with a list of criteria

    Quote Originally Posted by AlKey View Post
    It think OP is looking also for criteria codes in Column A should match the once in column E. But OP still din't confirm this.
    @Alkey,
    I missed it thanks for correcting me .

    Punnam

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  2. Sumif with criteria list
    By KL in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 AM
  3. [SOLVED] Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  4. [SOLVED] Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  5. [SOLVED] Sumif with criteria list
    By Brian Barbre in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2005, 08:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1