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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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,458
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*
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,638
Members
412,334
Latest member
ExcelForLifeDontHate
Top