# Lesson in coerced sumproduct please

#### babycody

##### Well-known Member
I have become a big fan of sumproduct. However there is something that I would like explained to me either through a post or a link. Why is it neccesary in some cases to coerce the ranges, but in other instances it isn't? I understand that coercing changes true to 1 and false to 0. Why would it make a difference to use one or the other. Doesn't TRUE TRUE get the same results as 1*1? Thanks all.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
an elaboration:

the syntax for sumproduct is:

SUMPRODUCT(array1,array2,array3, ...)

...but you will also notice another idiom in use:

sumproduct(array1*array2*array3)

...in the former, each individual array needs the logical to numeric coersion:

sumproduct(--(array1-condition),--(array2=condition),--(array3=condition))

In the latter, the multiplication does it anlong the way.

"It is in the nature of excel, and some other languages, that performing math operations on logicals will coerce them into numerics"

I am almost sure that you are giving away the secret in this statement. What is a logical? I still can't say that I see why you have to use it in some cases but not in others. You'll have to give this to me in the "Coerced Sumproduct for Dummies" version I'm afraid.

Here is where I show my true ignorance: : -> Does it have to do with using text instead of a numeric value in the ranges?

an elaboration:

the syntax for sumproduct is:

SUMPRODUCT(array1,array2,array3, ...)

...but you will also notice another idiom in use:

sumproduct(array1*array2*array3)

...in the former, each individual array needs the logical to numeric coersion:

sumproduct(--(array1-condition),--(array2=condition),--(array3=condition))

In the latter, the multiplication does it anlong the way.

So it all depends on the syntax I use for sumproduct to seperate the arrays and nothing more?

From what little I know, there is a difference between a comma syntax and asterisk syntax. When the ranges consist of a mix of vectors and matrices, you have to switch to the "star syntax". That means: A SumProduct formula with the "star syntax" instead of the native comma syntax or to an array formula.

The -- is just a coercer, which can be replaced by +0 or 1*

Replies
2
Views
207
Replies
3
Views
436
Replies
0
Views
499
Replies
4
Views
615
Replies
24
Views
519

1,218,538
Messages
6,143,078
Members
450,461
Latest member
Bosavon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back