How the SumProduct Formula Works

The sum product formula is officially used for summing the product of
two or more columns in an array. Here is a simple example.


Our formula:

=sumproduct(A1:A2, B1:B2)

Really translates to:

=(A1+B1) * (A2+B2)

*notice row 1 is adding to row 1, and row 2 to 2


(5*6)+(10*3) = 60

It is pretty straight forward.

Mutiple Conditional

You can also use the sumproduct formula as a super useful multi-conditional countif or sumif formula. Check out the table and how the formula is used. Please note that I am using named arrays in order to make this as simple as possible.


Super Countif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"))

The above use of the sumproduct formula will return the count of all the down owners in California that are named John. Cool isnt it? Very useful.

But what if you want to know the sum of the incomes of all the guys named John that live in California? Easy, add the range that has the income to the end of this. Like so.

Super Sumif

=sumproduct(--(name_array= John),--(location_array= CA),--(has_a_dog_array="yes"), --(income_array))

In this case the formula will match all the Johns that live in California and have a dog and tally up their incomes. So our result will be 123,000. You can also use conditionals if working with numbers.

Now I know the formula looks funny with all those negative signs but they are required as the formula will not work right if you omit them. In a couple of tests I have conducted I noticed that if the double negatives are removed the formula tries to multiply boolean (true/false) types instead of matching up what you are looking for. So though my observation of this is probably not ideal, remember the keep the double negatives in there for it to work right.

A very important thing to note is that when working with sumproduct formulas you must make sure your arrays are the same length. You cant have the name array be 100 rows and the location array be 95 rows high because it will cause an error.