Excel's Sumproduct Function

Excel's SUMPRODUCT function is more useful than it appears.  On its surface, SUMPRODUCT simply "returns the sum of the products of corresponding ranges or arrays." The easiest way to demonstrate this is to use the following table:

	A	B
1	5	3
2	2	6
3	0	10
4	1	8

The formula =SUMPRODUCT(A1:A4,B1:B4) yields 35, which is the sum of the products of columns A and B, calculated as follows: (5*3)+(2*6)+(0*10)+(1*8).

While this functionality alone can be useful in some contexts, the real power of SUMPRODUCT comes from utilizing criteria. The values in any column can be logically tested as TRUE or FALSE in Excel. The logical value TRUE is represented by the number one and the logical value FALSE is represented by zero.

With that in mind, we can analyze a larger table that lists a client's name, the business entities associated with that client, and the due date and fee amount of the annual renewal filing required by the state the entity is formed in:

	A	B	C	D	E	F
1	Client	Entity Name	Entity Type	State	Month Due	Amount
2	John Doe	ABC Corporation	Corp	UT	Apr	15
3	John Doe	Doe Family Partnership	LP	UT	Dec	15
4	John Doe	Doe Rental Property, LLC	LLC	DE	Jun	250
5	John Doe	Doe Equipment, LLC	LLC	DE	Jun	250
6	Bill Smith	Bill Smith, DDS, Inc.	Corp	NV	Jan	325
7	Bill Smith	Smith Family Partnership	LP	WY	Dec	50
8	Bill Smith	Smith Equipment, LLC	LLC	DE	Jun	250
9	Bill Smith	Smith Business Property, LLC	LLC	NV	Oct	325
10	Bill Smith	Smith Rental Property, LLC	LLC	NV	Oct	325
11	Bill Smith	Smith Vacation Home, LLC	LLC	WY	Feb	50

Suppose that Bill Smith is wondering what he will be paying each year for all his annual state renewal filings. This can be easily calculated with the following formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),F2:F11). The result is 1,325.

The array in column A must equal "Bill Smith" in order for the figure in column F to be included in the total. The first four rows do not equal Bill Smith so they return FALSE, and the remaining rows do equal Bill Smith so they return TRUE. The "--" turns the Boolean values TRUE and FALSE into the integer value 1 and 0 respectively. The products of the values from column A and the values in column F added together equal 1,325.

Of course, the above result is also possible with the following SUMIF formula: =SUMIF(A2:A11,"Bill Smith",F2:F11). However, SUMIF does a poor job of handling multiple criteria, while SUMPRODUCT can. Following are some additional examples of SUMPRODUCT formulas and the information they return.

To find the total fees due for Bill Smith's LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),F2:F11), which results in 950.

To find the total fees due for Bill Smith's Nevada LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),--(D2:D11="NV"),F2:F11), which results in 650.

To find what all clients will be paying for LLC fees in the month of June, use this formula: =SUMPRODUCT(--(C2:C11="LLC"),--(E2:E11="Jun"),F2:F11), which results in 750.

In summary, SUMPRODUCT simply returns the sum of the products of corresponding ranges or arrays. However, if multiple arrays are used as filters, its usefulness increases dramatically.

Posted in: Spreadsheet