A SUMPRODUCT question

[Bernard Liengme]

On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html
there is a note that the arrays in SUMPRODUCT formulas must be the same
size. Sounds sensible enough.

But today when look at the post with subject "Why does this formula return a
#VALUE error", we have one array of 12 by 1 and another of 12 by 12.

I have been using the double negative method of converting Boolean to {0,1}
but if I try
=SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and
the 1 by 2 arrays)
But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works!

So when is double negation (--) better than * (multiplication) ?

 

[RagDyeR]

Does this help at all?

http://tinyurl.com/y9u5qp


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

On the very useful site http://www.xldynamic.com/source/xld.SUMPRODUCT.html
there is a note that the arrays in SUMPRODUCT formulas must be the same
size. Sounds sensible enough.

But today when look at the post with subject "Why does this formula return a
#VALUE error", we have one array of 12 by 1 and another of 12 by 12.

I have been using the double negative method of converting Boolean to {0,1}
but if I try
=SUMPRODUCT(--(A1:F1="a"),A2:F3) I get a #VALUE error (note the 1 by 1 and
the 1 by 2 arrays)
But =SUMPRODUCT((A1:F1="a")*(A2:F3)) works!

So when is double negation (--) better than * (multiplication) ?

 

[Bernard Liengme]

I does explain that the 'star' syntax treats Array1*Array2 in a way similar
to MMULTI (explains that it DOES but not WHY, nor why the 'double neg' does
not)
But I have learnt (the old fashioned spelling should tell you something!) a
new thing today, so I shall enjoy my single malt tonight with a clear
conscience!
Many thanks, RagsDyeR
best wishes

 

[Dave F]

My understanding of the double negation syntax is that it converts text into
numbers (i.e., TRUE or FALSE) and that calculations/logical operations can
thereby be run on that conversion. For that purpose, I can see why you would
need the arrays to be a similar size.

But for the purposes I was using SUMPRODUCT for in the original post, I
don't see why the arrays would have to be of similar dimensions. And, as you
found out, they don't....

Dave