Slicing Data based on Multiple Criteria with SumProduct()

Excel’s SUMIF() Formula is a great tool.  It allows you to sum a column based on specific criteria (from another column).  But what if you want information filtered based on two (or more) criteria?

Enter SUMPRODUCT().

Let’s say you are managing a fundraiser for the sports programs at your son’s high school.  To motivate the otherwise lazy student-athletes, you are giving a party to the sport/class combination that sells the most $5 gift cards.

For each of the 200 student-athletes, then, you have four pieces of information:

1. Sport (Column B)
2. Class Year (Freshman, Sophomore, Junior, or Senior) (Column C)
3. Total Cards Sold (Column D)
4. Total Dollars Sold (not really an extra ‘field’ since all of the cards are $5 denominations, but included) (Column E

Now, Sumproduct is a unique (some might say weird, even) function.  It’s original intent was to eliminate multiple steps in gathering information.  Officially, Excel describes the function as “Returns the sum of the products of corresponding ranges or arrays”.  So, if you were trying to find out what the sum of column A * column B for rows 1 through 3, you’d enter =SUMPRODUCT(A1:A3,B1:B3). And if the two columns contained 1,2,3 and 4,5,6, respectively; then the formula would return 32 {(1*4) + (2*5) + (3*6)}

But SumProduct can do so much more, including working with multiple criteria.  So, back to our original example, the sports program fundraiser.  If you’d like to find out, for example, how many cards the Freshman Class sold, you could do this:

=SUMPRODUCT((D3:D202)*(C3:C202=”Freshman”))

Notice a few differences.  First, instead of commas, the different arrays or array criteria are separated by “*” (even though we are not really multiplying the arrays in this case)  Secondly, each array group is contained within its owned set of parenthesis.  Without these, the formula will produce an error.  And lastly of note, we can add as many criteria as we’d like (as long as they are not conflicting) and any array group that is not a criteria expression will be multiplied.  For instance, if we added column Columns B (with “Softball” as specified criteria) and E, it would look like this:

=SUMPRODUCT((E3:E202)*(D3:D202)*(B3:B202=”Softball”)*(C3:C202=”Freshman”))

This would look at only Freshman Softball players, but multiply the number of cards by the total dollars collected.  We obviously don’t want that, but you get the point.

Attached is a spreadsheet to help you better understand.
FundRaisingSampleData

Let me know if you have any questions.

Note: As with a lot of Excel functions and methods of doing things, there are other ways to perform the same task.  A couple of them are:

1. Array Formula with SUM()
2. In Excel 2007, you can perform this much easier with SUMIFS
3. Install Analysis TookPack and use the Conditional Sum Wizard