Post a Comment On: CPA at Law

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.