Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SumIf with multiple criteria (Excel 97 on Windows NT)

    A user here (a museum) wants to be able to calculate a total based on two criteria. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    The user has a spreadsheet that tracks various loans in which the Museum is interested. On a separate page she would like to have statistics: how many objects were lent to individual departments, how many prospective loans there are by department, how many canceled loans, etc.

    The total loans by department was easy with SumIf. However, trying to add the number of objects that are prospective loans by department has us both confused. Is there a way to tell Excel:

    1. Look in this column for "Prosp";
    2. If it is there, look in another column for "Dept";
    3. If the row contains both, sum the numbers of objects from specified column.

    I have attached an example workbook of what the user is seeking. Very simplified, but you'll get the idea (I hope).
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Put this ARRAY formula (confirm with ctrl-shift-enter) into Sheet2!C2:

    =SUM(IF((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$ E$11=A2),Sheet1!$D$2:$D$11))

    Copy it down the column

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    That would work if each loan only contained one object. Is there a way to sum a particular column if two columns match the criteria? In this case, loans are not necessarily for 1 object, there could be several pieces within the same loan.

    ???

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Sorry I noticed my mistake and corrected it BEFORE you posted!

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Meridith,
    I haven't opened your attached file but it sounds like a situation I just had. My workaround: I inserted another col and concatenated the 2 search columns. I then used the concatenation col for the sumif search range.

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    Long Beach, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    I like the concatenation solution. I use that a lot. Here is the array formula if you prefer.
    =SUM(IF(Sheet1!$A$2:$A$11="PROSP",IF(Sheet1!$E$2:$ E$11=A2,Sheet1!$D$2:$D$11)))

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Steve,

    I am not sure what I am doing wrong. When I copy your function into the example workbook I attached, all works fine. When I try to adapt it to the real workbook, it doesn't work.

    I printed the function and have tried to work through the text of the function, hoping that if I could understand the logic that I could fix the function on my own. Unfortunately, I haven't been able to do so. Could you run through the logic quickly so I could try and troubleshoot the function to work for my user?

    It would be a huge help. Thanks. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    You didn't specify what the problem is. Some important points:

    1) this is an ARRAY formula (confirm with ctrl-shift-enter):
    =SUM(IF((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$ E$11=A2),Sheet1!$D$2:$D$11))

    For additional info on array formulas, an excellent "primer" is:
    Chip Pearson's site: http://www.cpearson.com/excel/array.htm

    2) all three ranges defined in statement, must have the same dimensions (1 col and whatever rows)

    3) The A2 reference to compare to (Sheet1!$E$2:$E$11) in the current formula MUST be on the sheet with the formula. If it will be on a different sheet you need to give the sheet name(eg)
    =SUM(IF((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$ E$11=Sheet2!A2),Sheet1!$D$2:$D$11))

    4) Briefly, how they work:
    Think of a NEW column (Q) which contains in Q2:
    =Sheet1!A2="PROSP"
    This will yield either true or false
    Copy this down the column to Q11
    Now you have a col specifying whether or not the individual value in col a = "PROSP"

    Think of a NEW column which contains in R2
    =Sheet1!E2=$A$2
    This will yield either true or false
    Copy this down the column to R11
    Now you have a col specifying whether or not the individual value in col e = the value in A2 (locked)

    Think of a NEW column (S) which contains in S2
    =if(and(Q2,R2),D2)
    This will yield either the value in D2 or false
    Copy this down the column to S11
    Now you have a col giving either the values in Col D when BOTH conditions are true or the value FALSE

    In S1 imagine the formula:
    =sum(S2:S11)
    This will give you the sum of all the values in D when BOTH conditions are TRUE (which is your value of interest)

    The IF part of the ARRAY statement:
    (IF((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$E$11 =A2),Sheet1!$D$2:$D$11))
    Is essentially the formula in Col S
    Checks 2 conditions:
    (Sheet1!$A$2:$A$11="PROSP")
    and
    (Sheet1!$E$2:$E$11=A2)

    If they are BOTH true then you get the corresponding value from:
    Sheet1!$D$2:$D$11

    The result of this ARRAY formula is a 1 column array with 10 rows which are either the value from col D or False. The "*" operator works like AND (FYI, a "+" works like OR). This array is essentially IDENTICAL to the range S2:S11.

    Adding a SUM function:
    =SUM(IF((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$ E$11=A2),Sheet1!$D$2:$D$11))
    Gives you the value from S1 the sum of that array of values

    The great thing about the ARRAY formula is you got the answer without having to create extra intermediate columns!

    HTH,
    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Steve

    you can omit the function IF and you will obtain a better performance

    =SUM((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$E$1 1=A2),Sheet1!$D$2:$D$11)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    I think that you mean:
    =SUM((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$E$1 1=A2)*Sheet1!$D$2:$D$11)
    NOT
    =SUM((Sheet1!$A$2:$A$11="PROSP")*(Sheet1!$E$2:$E$1 1=A2),Sheet1!$D$2:$D$11)

    And yes that is shorter and will improve performance without the IF, BUT it is (IMHO) LESS generic.

    That trick ONLY works for SUMming the values.

    Using the IF in the form I use, allows it to be easily modified for COUNT, AVERAGE, MIN, MAX, STD, by just replacing the SUM. So I prefer using that way at the expense of some performance.

    Steve

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    How about a non-array formula entered in sheet 1?

    =sumproduct((A2:A11="Prosp")*(E2:E11=sheet2!a2),D211)

    Sorry for butting in!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Yes that has the advantage of being an array formula that is not entered as one (sumproduct, works with arrays, but does not have to be entered like one).

    It answers the original question, but as I mentioned in the previous post, this one also ONLY works for SUM. It can not be used for the othr stat functions.

    Steve

  13. #13
    Star Lounger
    Join Date
    Apr 2001
    Location
    new york city
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SumIf with multiple criteria (Excel 97 on Windows NT)

    Thank you so much for the assistance with Array formulas. I, obviously, had never worked with them before; the assistance was extremely helpful. I would never have figured it out on my own. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Thanks again!

Posting Permissions

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