Lesson in coerced sumproduct please

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
"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. :LOL: 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?
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
PaddyD said:
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?
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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*
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,052
Messages
5,835,142
Members
430,343
Latest member
Sailingexcel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top