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

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

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

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

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

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.

"Epinn" wrote in message
...

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.

Lots of people respond like that. I visit a discounts forum, and inevitably
I am told to google a list of suppliers for the product. But the point of my
posting is to get what people recommend, any idiot can google and get a
list, but what do you do then with that info. People are strange <bg


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


Of course, you don't have to ask me, it is your choice. I was just trying to
promote one of my hobby horses, that of using comma before an array of
values.

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

No Epinn, that is not what I am saying. I am saying, don't even try to get
the help changed. Help is correct, it just stops short of how we use it. On
the other point, there are no absolutes. I prefer the --, RagDyer who is no
slouch with this stuff swears by *. Get comfortable with your preference and
stick with it.

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?


As I said, don't worry. Use what you feel comfortable with, as I do.


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


Stick with it,.it's all fun, and good for the brain cells.

BTW, what nationality are you. Epinn is not a name I have come across
before.

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

When you have text interspersed with the numeric values you are summing, or
when you have included the headers in your ranges and your headers are text.
:-)

Regards
Ken..............................


<snip

Okay, one step forward and one step backward ... ;) This sounds like something beyond me.

On September 7, someone (jv4_2+1) posted this question:-

"I have a range of cells and in that range if I enter any type of text/number
I want the rest of the row that cell belongs to, to be blacked out or marked
somehow to show that, that row has an entry in it already."

The answer submitted by Max in Singapore is this.

"Assume range is A1:D5

Select A1:D5 (with A1 active)
Click Format Conditional Formatting

Under Condition 1, make the setting as:
Formula is:
=SUMPRODUCT(--MATCH(TRUE,$A1:$D1<"",0))<COLUMN(A1)
Click "Format" Patterns tab Black OK
Click OK at the main dialog"

Wow! SUMPRODUCT and double negating!! I have no idea how to interpret the above. But somehow I wonder if the above formula is an example of the situation that Bob and Ken talk about where only negating works and "*" won't. I shall try to play with it. The poster did mention data might be text/number.

Wonder what a **simpler** alternative formula will be? Since it is a range, probably needs an array formula?

Epinn

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

When you have text interspersed with the numeric values you are summing, or
when you have included the headers in your ranges and your headers are text.
:-)

Regards
Ken..............................


<snip