# Thread: SUMIF with multiple criteria (Excel 2003)

1. ## 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. ## 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. ## 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)

4. ## 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
•