xldynamic is back
 

Hello Bob,

My email to your webmaster account bounced back.

I suggested to change your explanation / example for SUNPORODUCT for
an OR condition to change from

=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))

to

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")))

because Fords in June would get double counted.

Kind regards,
Bernd

xldynamic is back
 

Unfortunately, in my host move I lost some files, so this was an old
version. Also it seems my previous host still holds that email even though
they don't have the domain (sic!), so I have had to change it

I have updated the whole page, tidied some sections up, added 3 new sections
on SUMPRODUCT and VBA, Excel 2007, and Performance, and also split it into 2
pages, the discussion and the examples.

--
__________________________________
HTH

Bob

"Bernd P" wrote in message
...
Hello Bob,

My email to your webmaster account bounced back.

I suggested to change your explanation / example for SUNPORODUCT for
an OR condition to change from

=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))

to

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")))

because Fords in June would get double counted.

Kind regards,
Bernd

xldynamic is back
 

Hello Bob,

Hmm, the OR example is ok now but still a little bit misleading
because you can't just add the criteria comparisons for overlapping
criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or
you might end up double counting overlaps).

Regards,
Bernd

xldynamic is back
 

It is not trying to be an example for different ranges, but for the same
range. Therefore as it cannot be both conditions there is no need to avoid
duplicate counting. At this point it is just introducing OR, to add the
further complication of duplications would be too soon IMO. And SIGN is not
the only way to test for duplicates, so it doesn't have to be SIGN.

--
__________________________________
HTH

Bob

"Bernd P" wrote in message
...
Hello Bob,

Hmm, the OR example is ok now but still a little bit misleading
because you can't just add the criteria comparisons for overlapping
criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or
you might end up double counting overlaps).

Regards,
Bernd

xldynamic is back
 

Hello Bob,

Ok, let us disagree again.

IMHO your OR example is misleading.

Regards,
Bernd

PS: I am open to learn something more convincing than SIGN to make the
sum of condition checks safe for an OR:
http://sulprobil.com/html/sumproduct.html

On 28 Sep., 19:12, "Bob Phillips" wrote:
It is not trying to be an example for different ranges, but for the same
range. Therefore as it cannot be both conditions there is no need to avoid
duplicate counting. At this point it is just introducing OR, to add the
further complication of duplications would be too soon IMO. And SIGN is not
the only way to test for duplicates, so it doesn't have to be SIGN.

--
__________________________________
HTH

Bob

"Bernd P" wrote in message

...

Hello Bob,

Hmm, the OR example is ok now but still a little bit misleading
because you can't just add the criteria comparisons for overlapping
criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or
you might end up double counting overlaps).

Regards,
Bernd