Bob,

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.

Allow me to clarify. I was **never** under the impression that you accused
me of being lazy. When I wrote that I had someone else (from another forum,
yes from a non Excel forum) in mind who complained about me posting my very
first SUMPRODUCT question instead of using Help. We all know that
SUMPRODUCT is such an evolution and you said it in the paper that we
wouldn't be able to find the info from Excel Help.

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.

I heard you, Bob and I understand the examples you gave. But, please
(please, please) let me use "*" cause I really like it and I don't want to
think when I should use "*" and when ",".

...... 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.

Forgive me for not being very good at comprehension or haven't digested it
fully. "No, no, absolutely not" means NOT to change "," to "*" in the
original syntax? This is what you tried to tell me, right? I know there is
NO NEED, because I understand your explanation. But like I said, I really
like "*" especially when it works and I can avoid picking and choosing.
Mind you I am totally ignoring double negating. Okay, not to "upset" you
too much, may be I'll try not to touch the syntax in "Help" but for
conditional testing ...... It is interesting that I am bargaining for the
use of "*". Funny, eh?

Glad you didn't feel this was a chore and found pleasure in helping others.
I think this is the essence of the MVP program. It is gratifying to know
that there are good people, like yourself and many others, out there who are
knowledgeable, generous and supportive. I should refrain from posting until
I have **analyzed** the paper in full or just run away from SUMPRODUCT() if
it starts to consume my life. <bg

Thank you for listening.

Epinn

"Bob Phillips" wrote in message
...

"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.