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.)
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. 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 "*". 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.
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."
Thank you all for putting up with me and listening. It helps to talk about
it when I am confused.
Epinn
"Bob Phillips" wrote in message
...
Epinn,
The basic format of SP is
=SUMPRODUCT(array1,array2,array3, ...)
such as =SUMPRODUCT(A1:A10,B1:B10)
so that just multiplying two ranges of numeric values just needs to be
separated by a comma.
However, as the xldynamic page shows, when you introduce a conditional
test
in SP, it is not a range of values that is being evaluated, but an array
of
TRUE/FALSE values. However, multiplying an array of TRUE/FALSE by an array
of numeric values, will just produce 0, so you need to COERCE that array
of
TRUE/FALSE to an array of numeric values, which would be 1/0, so the
multiply format works fine. This can be done by many mathematic operators,
such as *1, +0, N, or -- (again explained in the xldyanmic page).
If you have more than one condition in the SP, you can simply multiply one
array of TRUE/FALSE results by the other array of TRUE/FALSE results, to
get
a resultant array of 1/0. But you could just use the double unary (or any
of
the other methods). So
(rng1=condition1)*(rng2=condition2)
is equivalent to
--(rng1=condition1),--(rng2=condition2)
When you introduce an array of values, there is no need to coerce this
array
to numeric values, it is already numeric values. So there is no need to
precede it by a mathematical operator, a simple comma will suffice. So you
can have
(rng1=condition1)*(rng2=condition2), rng3
or its equivalent to
--(rng1=condition1),--(rng2=condition2),rng.
However, when you have just one condition, the second form still stands up
--(rng1=condition1),rng3
because the double unary coerces the TRUE'FALSE array. However the first
form would not have that mathematical operator with one condition, so you
need to use it against the array of values
rng1=condition1*rng3
Read that paper, it explains it.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Epinn" wrote in message
...
Bernard,
Thank you for the links. I am still struggling with SUMPRODUCT(), two
steps
forward and one step backward.
I enjoyed Ken's illustration (same thread) and I also read this
http://www.officearticles.com/excel/...rosoft_excel.h
tm
Just when I think I am seeing light at the end of the tunnel, I read the
second link
http://mcgimpsey.com/excel/formulae/doubleneg.html
Then I get so confused with all the "coercing," "double negating" etc.
from
the article.
Please refer to the above link. I have problem understanding this
formula
=SUMPRODUCT(--(A1:A510),B1:B5))
This is the first time I see two minus signs side by side and a comma
","
instead of a "*" between the arguments/arrays.
The following formula will give me the exact same result and I would
like
to
embrace it as I understand it.
=SUMPRODUCT((A1:A510)*(B1:B5))
I haven't studied the first link in detail (I just skimmed through it)
as
I
don't want to overwhelm myself and get more confused. When I am more
experienced, I am sure I can appreciate it as an excellent resource.
Feedback welcome on coercing, double negating etc.
Epinn
"Bernard Liengme" wrote in message
...
Read these two
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Epinn" wrote in message
...
Please refer to Ntobias' thread on Aug 24, 2006 and Sandy's comment
before
you read on.
Sandy,
Thank you for your comment and a different perspective. Before JE's
post,
I
already had a hard time understanding SUMPRODUCT. When I read JE's
post
I
did more research. Excel help was not a big help and I got lost in
cyber
space encountering things like matrix Algebra. To make a long story
short -
I am getting more confused with SUMPRODUCT and not sure if I
understand
it.
Can someone point me to some good and straightforward tutorial
please?
I found the following examples from the net but unfortunately there
was
no
step by step explanation. I thought it was a good idea to use
Excel's
ToolsFormula AuditingEvaluate Formula to analyze the formulas so
that
I
could have a clue of what's going on. Feel free to laugh.
1 =SUMPRODUCT(1,2,3) = 6
2 =SUMPRODUCT({1,2},{2,3}) = 8
3 =SUMPRODUCT({1,2,3,4}) = 10
4 =SUMPRODUCT({1,2,3,4},{4,3,2,1}) = 20
5 =SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7;5,3}) = 156
6 =SUMPRODUCT(MMULT({1,2;3,4},{1,2;3,4})) = 54
7 =SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7}) = #VALUE!
8 =SUMPRODUCT((A1:A20=5)*(A1:A20<=10)*A1:A20)
I truly need some **simple and straightforward** guidance. Please
bear
in
mind that I am a very new user. By the way, I am also trying to
learn
by
looking at the big picture. For example, when I look at a
SUMPRODUCT
formula, I try to think of the **equivalent** and more flexible SUM
(array)
formula. You guess it, I get more confused.
Help!! Appreciate feedback.
Epinn