"Epinn" wrote in message
...
Bob,

Thank you for being so kind and taking the time to explain things. After
I
posted last time, I did check out a couple of paragraphs of that paper in
more detail and I realized that "," and "*" discussion was a big thing.
(Originally, I was afraid of getting more confused and tried to avoid the
paper. It may not be that bad and I have decided not to feel
"intimidated"
by it. Please note that I wasn't lazy but tried to stick to the KISS
method. Looks like this method may not work in the circumstances.)


It's my pleasure Epinn. Being about to expound in such a manner is pleasing,
so I didn't consider it a chore.

And I wasn't accusing you of being lazy, even if it sounded so, it is just
that from your questions I felt you hadn't fully digested it, and it is
worth the effort.

Do note though that it is not really "," and "*" that is a big thing, it is
"*" OR "--" OR "+0" OR ... etc. The "," is a bit of a diversion really, but
an interesting one.


I haven't got the entire picture nor **fully digested** your post yet.
But
from my own experiments, I found that "*" always gave me the correct
answer.
If I change the basic format from "," to "*" it will still work. If I
change from "*" to "," it may not work.


I understand that, and indeed it states in the xldynamic paper that

.... There is no situation that I know of whereby a solution using -- could
not be achieved somehow with a '*'. Conversely, if using the TRANSPOSE
function within SUMPRODUCT, then the '*' has to be used...

I actually do know of one situation where "*" doesn't work and -- does, but
I can't recall it now <bg.


So, at this moment, I am under the
impression that it may be simpler to use "*" ALL THE TIME. I even want to
change the syntax on Help to "*".


No, no, absolutley not (the help that is). Help refers to SUMPRODUCT in the
way that MS designed it to work. In its original form, SUMPRODUCT does the
mutiplying (that is what the PRODUCT part means), so there is absolutely (my
favourite word) no need to include it normally. As I said before, the
evolved use of SP allows the inclusion of conditional tests, and it is these
conditional tests that need to be coerced, and "*" is just one way of doing
that. When MS designed SUMPRODUCT, they never imagined the use it is put to,
it is creative individuals here that took it so much further.


I know I must use "*" if conditional test
is involved as I did understand Ken's previous explanation of True/False
(1/0). To keep things simple, I may want to ignore double negating and
comma, and just stick with "*" After I have studied the paper and fully
understood how things are supposed to be, I may throw away what I just
said.

That is exactly my point Epinn, there is no MUST. You have to use something,
but not necessarily "*", not necessarily "--". Take your pick.

But there is nothing wrong with sticking to "*", the only thing I would ask
(as a favour to me <ebg) is that even if you do use "*", don't precede a
single range of values by "*" unless there is no other "*" in the formula.

Therefore

=SUMPRODUCT((rng1="Bob")*rng3)

okay, you have to as there is no coercion of the condition otherwise. But

=SUMPRODUCT((rng1="Bob")*(rng2<TODAY())*rng3)

is unnecessary as there is already coercion of the c onditional tests, so
you only need

=SUMPRODUCT((rng1="Bob")*(rng2<TODAY()),rng3)

which achieves the same result, and is closer to the true SP syntax.


Bob, are you the author of the paper? I searched the web site and
couldn't
find the verification. Anyway, I want to thank the author for such a
detailed article with so many examples. The following lines from the
paper
gave me incentive to study the paper and truly learn about SUMPRODUCT().
Hopefully, I can replace SUM(IF()), SUMIF() etc. with SUMPRODUCT().

"......this paper is focusing on one particular function, the SUMPRODUCT
function, which by creative use has evolved a flexibility undreamt of by
its
originators in Microsoft."


I have to admit guilty there my friend. I wrote it when many people asked
the sort of question that you asked (although few have been as tenacious in
making sure that they do understand - good on you). When you do read it all,
you will also see that I use the Ken Wright explanantion that you refer to,
and Ken gets accreditation for that.

Be careful replacing SUMIF with SUMPRODUCT, it is not always appropriate.
For instance,

=SUMPRODUCT((rng1="Bob")*rng3)

and

=SUMI(rng1,"Bob",rng3)

will give the same correct result, but IMO you should not use SP, it is
orders of magnitude less efficient that SUMIF.

However, SUMIF is less flexible, so you can't do

=SUMIF(LEFT(rng1,3),"Bob",rng3)

whereas you can do

=SUMPRODUCT((LEFT(rng1,3)="Bob")*rng3)



Thank you all for putting up with me and listening. It helps to talk
about
it when I am confused.


As I said, it has been a pleasure, I enjoyed it. I hope you got some
pleasure also.