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