sumproduct explained with double unary operator (2003)

Topic

New Reply

Hi,

I’m planning on taking a course later this year and one of the pre-requities is to know how to use SUMPRODUCT. I use pivot tables extensively but haven’t used SUMPRODUCT before.

I found some information on it and tried to construct a table with months across the top and divisions down the left side. I wanted the SUMPRODUCT to calculate how many claims each division had in each month (from a flat data source).

I range named the columns that contained the data, then tried
^=SUMPRODUCT((Division=$B$5 )*(date=C4)*(claims))
this did not work and I tried several other variations that did not work.

In one place looking for answers I found something about double unary operators so I tried
first with range names then without and found that

^=SUMPRODUCT(–(‘Data’!$H$2:$H$2500=$B$5 ),–(‘Data’!$S$2:$S$2500=C4),’Data’!$BW$2:$BW$2500)

worked.

I’m trying to understand how the function works, but keep hitting dead links when looking for an explanation. Can someone tell me if what I understand is correct?

If the arguments in the first 2 places are not numbers, then you have to use the double unary operator. If they are numbers then you can use the first format. This doesn’t make sense to me, because you are only trying to get the aggregate amount for conditions that match your criteria (eg =B5, =C4) so the 3rd arguments is the only one that is being summed. What difference does it make whether the criteria is a number or text?

Thanks for any help with understanding this function.

capri

Reply | Quote

Replies

Edited by HansV to correct mistake

The result of (‘Data’!$H$2:$H$2500=$B$5) is an array of TRUE/FALSE values – TRUE for cells in the range ‘Data’!$H$2:$H$2500 that match $B$5, FALSE for those that don’t.
If you use such an array as an argument in SUMPRODUCT, the result will be 0 because TRUE/FALSE are logical values, not number values.
BUT … Excel stores TRUE as 1 and FALSE as 0, and as soon as you apply any numeric operation to a TRUE/FALSE value, it is treated as 1/0.
So -(‘Data’!$H$2:$H$2500=$B$5) becomes an array of -1 and 0, and –(‘Data’!$H$2:$H$2500=$B$5) an array of 1 and 0. This can be used in SUMPRODUCT to count or sum other values.
Each cell that matches the condition contributes 1 and each cell that doesn’t match the condition contributes 0.
If you multiply two or more of such arrays, you only get a 1 in the places where each value is 1, i.e. where all conditions are met. If at least one value is 0, the product is 0.
So the entire SUMPRODUCT formula will add the values from the last argument (that doesn’t contain a condition …=…) for which all conditions in the previous arguments are met.

You should also be able to use

=SUMPRODUCT((‘Data’!$H$2:$H$2500=$B$5 )*(‘Data’!$S$2:$S$2500=C4)*’Data’!$BW$2:$BW$2500)

because the multiplication operator * forces the TRUE/FALSE values in the first two arrays to be interpreted as 1/0.

Reply | Quote

There is additionally a small difference between how the two handle text in the data (as opposed to criteria) range:

=SUMPRODUCT(--('Data'!$H$2:$H$2500=$B$5 ),--('Data'!$S$2:$S$2500=C4),'Data'!$BW$2:$BW$2500)

will work if ‘Data’!$BW$2:$BW$2500 contains text (such as “” as the result of a formula), whereas:

=SUMPRODUCT(('Data'!$H$2:$H$2500=$B$5 )*('Data'!$S$2:$S$2500=C4)*'Data'!$BW$2:$BW$2500)

will fail due to the direct multiplication.
This should also work as the text values are not directly multiplied:

=SUMPRODUCT(('Data'!$H$2:$H$2500=$B$5 )*('Data'!$S$2:$S$2500=C4),'Data'!$BW$2:$BW$2500)

FWIW.

Reply | Quote

Hi Hans, Rory,

I don’t understand something about this:

in the original post, the third arg to SUMPRODUCT was ‘Data’!$BW$2:$BW$2500.

Rory suggests that the approach of 3 args to SUMPRODUCT, as opposed to 1 arg that is a multiplication of the conditions and the data, works in cases that the data contains text because it avoids a direct multiplication.

But doesn’t the SUMPRODUCT multiply each corresponding element of each arg by the same element of the other args. So true/false [from the first arg] * true/false [from the second arg] * value of data [from the third arg] seems to be the same as providing 1 arg as a multiplication of arrays. How are these different?

Also, now having seen a few examples of SUMPRODUCT, I’m confused as to when to use multiple args for SUMPRODUCT vs when to combine the arrays into 1 arg for SUMPRODUCT. Rory’s post may be at least a partial explanation but, per above, I don’t get that either.

HANS:
Is your line in your posting that says

>>Each cell that matches the condition contributes 0 and each cell that doesn’t match the condition contributes 0.

correct? Should there be 2 zeros?

Thanks.

Fred

Reply | Quote

Sorry, that was a typo, it should have been

Each cell that matches the condition contributes 1 and each cell that doesn’t match the condition contributes 0.

Reply | Quote

Take a look at the attached sample workbook. Each block contains three formulas illustrating the use of multiple comma-separated arguments and of arguments multiplied together. The third formula in each block doesn’t return the desired value. The third column in the orange block contains a text value instead of a number, causing the second formula to return an error value.

Reply | Quote

Hi Hans,

I see the examples. But the failure of Formula2 with a text value and multiplication of all 3 args goes back to my original question a few posts ago – how is SUMPRODUCT multiplying the corresponding elements of each array when using 3 args in Formula1 different than me supplying 1 arg as the multiplication of the 3 arrays? When I look at Formula1 using F9, I see an array {10;20;”what”;40}.

And also, is the incorrect value in Formula3 in both cases (green and orange) due to the failure to convert TRUE/FALSE to a numeric value as done in Formula1 by the multiplication of the first two array as 1 arg?

TIA

Fred

Reply | Quote

When using =SUMPRODUCT(array1,array2), Excel ignores text values in array1 and array2, just like =SUM(range) ignores text values.
But the * operator in =SUMPRODUCT(array1*array2) does *not* ignore text values, it returns an error value when it encounters one, and hence SUMPRODUCT too.

Formula3 does indeed return 0 because the individual TRUE/FALSE arrays aren’t converted to numeric. Using array1*array2 forces Excel to convert the values to numeric before multiplying them.

Reply | Quote

Thanks for the explanation, Hans.

Fred

Reply | Quote

Thanks Hans for the explanation and sample.

capri

Reply | Quote