Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF with multiple criteria (Excel 2003)

    I'm using SUMIF to total a list of items with $. How can I extend this to select multiple columns for the criteria?

    Example: Col A has 1 of 10 names, col B has 1 of 3 names. Using a combobox from the Controls Toolbox, I have these set to the list of possible choices (10 items and 3 items respectively). I want the user to choose one item from each list and the formula will calculate the total $ for all rows that match this criteria pair.
    <pre>Joe, Shoes, $100
    Joe, Shirt, $50
    Sue, Shirt, $75
    Mike, Shoes, $75
    Joe, Shoes, $50
    </pre>

    So if the user chose "Joe" from list 1 and "Shoes" from list 2 the total calculated would be $150.

    How would I use SUMIF (if that's the appropriate formula) to specify the 2nd field, criteria, is two items? I can't get it to work the way I've done it with AND() but I'm sure there is some way to work it.

    Thnx, Deb

  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 2003)

    There are some example techniques at:Counting Records Matching Multiple Criteria as well as Excel: SumIf with Multiple Criteria. Excel Formulas & Functions. Also there are many examples on this board by Searching for sumif multiple criteria.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF with multiple criteria (Excel 2003)

    There are various solutions. Let's say the bound cell for the first combo box is P1, and the bound cell for the second combo box is Q1.

    For example the array formula (confirm with Ctrl+Shift+Enter)

    =SUM(IF((A1:A100=P1)*(B1:B100=Q1),C1:C100))

    or the 'normal' formula

    =SUMPRODUCT((A1:A100=P1)*(B1:B100=Q1)*C1:C100)

    (If you search for sumif multiple criteria you'll find many previous threads about this subject)

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF with multiple criteria (Excel 2003)

    Pefect! This will be very helpful for several things I want to do (and avoid VBA).

    Steve - I went through those links you provided and learned a lot. I'm experiment with all the options and will keep them in my tricks bag.

    Thnx, Deb

Posting Permissions

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