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

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

As per Bernard's reply, Bob's page should hopefully cover it for you,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

but if you just want a basic explanation of how it works then try this:-

Imagine a range of data A1:C9 with Row 1 being titles

A B C
1 Name WorkNo Hours
2 Jim CDE456 6
3 Jim CDE456 2
4 John ABC123 4
5 John ABC123 9
6 Jim BCD444 7
7 John BCD444 6
8 John BCD444 6
9 John BCD444 6

The name bit is obvious, the workno or workpackage is a charge number that
an employee would have been given to book the time he works to, whilst
working on a specific project, and the hours are obviously the hours he
actually worked on that project. The reason you would have more than one
entry for each person is that these might represent different days. The
business need is to total all the hours for each person by WorkNo and then
add them all up so you can charge the customer for the right amount of hours
spent on his project.

The following formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 )) evaluates to the
following:-

The system first looks at all the entries in Col A to work out the
(A2:A9="John") bit, and evaluates whether or not the statement is TRUE or
FALSE. ie it goes down and looks at A2 and says is the name = John? answer
FALSE, Down to A3 and is the name = John? answer FALSE, Down to A4 and is
the name = John? answer TRUE and so on.

Then it does the same for the second statement (B2:B9="ABC123) ie it goes
down and looks at B2 and says is the WorkNo = ABC123? answer FALSE, Down to
B3 and is the WorkNo = ABC123? answer FALSE, Down to B4 and is the WorkNo =
ABC123? answer TRUE and so on.

The third piece does not have a condition in it, so each entry in the range
stays as it is, ie C2 = 6, C3 = 2, C4 = 4 and so on.

Now having done all that, Excel has created in it's memory a table that
looks like this:-

A B C
1 Name WorkNo Hours
2 FALSE FALSE 6
3 FALSE FALSE 2
4 TRUE TRUE 4
5 TRUE TRUE 9
6 FALSE FALSE 7
7 TRUE FALSE 6
8 TRUE FALSE 6
9 TRUE FALSE 6

but in Excel, TRUE evaluates to 1, whilst FALSE evaluates to 0, so the table
really looks like this to Excel:-

A B C
1 Name WorkNo Hours
2 0 0 6
3 0 0 2
4 1 1 4
5 1 1 9
6 0 0 7
7 1 0 6
8 1 0 6
9 1 0 6

Now Excel uses the * signs in the formula which are really just
multiplication signs to decide what to do with each of the values, so the
formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 ))

means multiply the first column by the second column and then muultiply that
by the third column, ie


A B C
1 Name WorkNo Hours
2 0 * 0 * 6 = 0
3 0 * 0 * 2 = 0
4 1 * 1 * 4 = 4 <<<<<
5 1 * 1 * 9 = 9 <<<<<
6 0 * 0 * 7 = 0
7 1 * 0 * 6 = 0
8 1 * 0 * 6 = 0
9 1 * 0 * 6 = 0

As you can see, because of the 1s and 0s, the only bits of the equation that
do not equal 0 are those that satisfy the criteria, which are rows 4 and 5,
and if you add them together (which the formula does for you) you will get
13. This means that you can bill the customer for 13 hours of work that
John has spent on project ABC123.

I have only listed 3 columns but you could have more if you wanted.


Hope this helps, but by all means post back if you need any more
explanation.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"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

Ken,

I can't thank you enough. You are such a good teacher. I love details and
I don't think your illustration can be any more detailed than this. You
really opened my eyes. The example you gave was different from the Excel
help, from the samples I listed in my previous post, from JE's formula (not
entirely though). I didn't know that SUMPRODUCT could actually be used to
filter data and we didn't even have to sort first. Wow! So dynamic.

I will check the links posted by Bernard. Thanks, Bernard. I have a
feeling that **eventually** I will figure out how to create SUMPRODUCT
formulas. That may be easier than looking at a SUMPRODUCT formula and
determining whether it is addition or multiplication. I am referring to the
sample formulas that I posted in my previous post. Any comments?

When I learn, I try to look at the big picture. I have been wondering if
SUMPRODUCT() can be included in the following link as the sixth method.

http://office.microsoft.com/en-us/as...366211033.aspx

I doubt it because it only gives one grand total instead of various
subtotals. Please correct me if I am wrong. I see that I can use
SUMPRODUCT() to verify the grand total generated by any of the five methods.

Thanks again. Hopefully, I can learn from you often in the future. Good
teachers are precious and few.

I may post back if I have any questions after checking out the links.

Cheers,

Epinn

"Ken Wright" wrote in message
...
As per Bernard's reply, Bob's page should hopefully cover it for you,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

but if you just want a basic explanation of how it works then try this:-

Imagine a range of data A1:C9 with Row 1 being titles

A B C
1 Name WorkNo Hours
2 Jim CDE456 6
3 Jim CDE456 2
4 John ABC123 4
5 John ABC123 9
6 Jim BCD444 7
7 John BCD444 6
8 John BCD444 6
9 John BCD444 6

The name bit is obvious, the workno or workpackage is a charge number that
an employee would have been given to book the time he works to, whilst
working on a specific project, and the hours are obviously the hours he
actually worked on that project. The reason you would have more than one
entry for each person is that these might represent different days. The
business need is to total all the hours for each person by WorkNo and then
add them all up so you can charge the customer for the right amount of
hours
spent on his project.

The following formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 )) evaluates to the
following:-

The system first looks at all the entries in Col A to work out the
(A2:A9="John") bit, and evaluates whether or not the statement is TRUE or
FALSE. ie it goes down and looks at A2 and says is the name = John? answer
FALSE, Down to A3 and is the name = John? answer FALSE, Down to A4 and is
the name = John? answer TRUE and so on.

Then it does the same for the second statement (B2:B9="ABC123) ie it goes
down and looks at B2 and says is the WorkNo = ABC123? answer FALSE, Down
to
B3 and is the WorkNo = ABC123? answer FALSE, Down to B4 and is the WorkNo
=
ABC123? answer TRUE and so on.

The third piece does not have a condition in it, so each entry in the
range
stays as it is, ie C2 = 6, C3 = 2, C4 = 4 and so on.

Now having done all that, Excel has created in it's memory a table that
looks like this:-

A B C
1 Name WorkNo Hours
2 FALSE FALSE 6
3 FALSE FALSE 2
4 TRUE TRUE 4
5 TRUE TRUE 9
6 FALSE FALSE 7
7 TRUE FALSE 6
8 TRUE FALSE 6
9 TRUE FALSE 6

but in Excel, TRUE evaluates to 1, whilst FALSE evaluates to 0, so the
table
really looks like this to Excel:-

A B C
1 Name WorkNo Hours
2 0 0 6
3 0 0 2
4 1 1 4
5 1 1 9
6 0 0 7
7 1 0 6
8 1 0 6
9 1 0 6

Now Excel uses the * signs in the formula which are really just
multiplication signs to decide what to do with each of the values, so the
formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 ))

means multiply the first column by the second column and then muultiply
that
by the third column, ie


A B C
1 Name WorkNo Hours
2 0 * 0 * 6 = 0
3 0 * 0 * 2 = 0
4 1 * 1 * 4 = 4 <<<<<
5 1 * 1 * 9 = 9 <<<<<
6 0 * 0 * 7 = 0
7 1 * 0 * 6 = 0
8 1 * 0 * 6 = 0
9 1 * 0 * 6 = 0

As you can see, because of the 1s and 0s, the only bits of the equation
that
do not equal 0 are those that satisfy the criteria, which are rows 4 and
5,
and if you add them together (which the formula does for you) you will get
13. This means that you can bill the customer for 13 hours of work that
John has spent on project ABC123.

I have only listed 3 columns but you could have more if you wanted.


Hope this helps, but by all means post back if you need any more
explanation.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*------------
----
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*------------
----


"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

I am still thinking how I can have subtotals for each person without any
"hardcoding." For simplicity, let's just drop subtotals for workno for now.
May be I can use advanced filter and make a separate list of names. That's
it. Advanced filter (unique records only) + SUMPRODUCT. (I only got it
while typing this.) But it won't be the sixth method, still one of the five
methods. By the way, I didn't check the list of the five methods. All of a
sudden, the light bulb just .......

Would you please confirm that I am on the right track. Do I just put the
formula (taking out "John," workno check etc.) in one cell beside the first
name of the unique name list and then double click on the handle to copy it
down the list of names? I think I can experiment too after I have given my
brain a rest.

I don't want to torture my brain right now by throwing in workno. But I do
welcome your comments. May be I should forget about SUMPRODUCT and just go
with PivotTable which is more dynamic. I think SUMPRODUCT by itself can be
dynamic in generating one total, but to combine it with advanced filter it
is probably not worth it.

Thank you for reading when I think aloud. Please feel free to educate me.

Epinn

"Epinn" wrote in message
...
Ken,

I can't thank you enough. You are such a good teacher. I love details
and
I don't think your illustration can be any more detailed than this. You
really opened my eyes. The example you gave was different from the Excel
help, from the samples I listed in my previous post, from JE's formula
(not
entirely though). I didn't know that SUMPRODUCT could actually be used to
filter data and we didn't even have to sort first. Wow! So dynamic.

I will check the links posted by Bernard. Thanks, Bernard. I have a
feeling that **eventually** I will figure out how to create SUMPRODUCT
formulas. That may be easier than looking at a SUMPRODUCT formula and
determining whether it is addition or multiplication. I am referring to
the
sample formulas that I posted in my previous post. Any comments?

When I learn, I try to look at the big picture. I have been wondering if
SUMPRODUCT() can be included in the following link as the sixth method.

http://office.microsoft.com/en-us/as...366211033.aspx

I doubt it because it only gives one grand total instead of various
subtotals. Please correct me if I am wrong. I see that I can use
SUMPRODUCT() to verify the grand total generated by any of the five
methods.

Thanks again. Hopefully, I can learn from you often in the future. Good
teachers are precious and few.

I may post back if I have any questions after checking out the links.

Cheers,

Epinn

"Ken Wright" wrote in message
...
As per Bernard's reply, Bob's page should hopefully cover it for you,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

but if you just want a basic explanation of how it works then try this:-

Imagine a range of data A1:C9 with Row 1 being titles

A B C
1 Name WorkNo Hours
2 Jim CDE456 6
3 Jim CDE456 2
4 John ABC123 4
5 John ABC123 9
6 Jim BCD444 7
7 John BCD444 6
8 John BCD444 6
9 John BCD444 6

The name bit is obvious, the workno or workpackage is a charge number
that
an employee would have been given to book the time he works to, whilst
working on a specific project, and the hours are obviously the hours he
actually worked on that project. The reason you would have more than
one
entry for each person is that these might represent different days. The
business need is to total all the hours for each person by WorkNo and
then
add them all up so you can charge the customer for the right amount of
hours
spent on his project.

The following formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 )) evaluates to the
following:-

The system first looks at all the entries in Col A to work out the
(A2:A9="John") bit, and evaluates whether or not the statement is TRUE
or
FALSE. ie it goes down and looks at A2 and says is the name = John?
answer
FALSE, Down to A3 and is the name = John? answer FALSE, Down to A4 and
is
the name = John? answer TRUE and so on.

Then it does the same for the second statement (B2:B9="ABC123) ie it
goes
down and looks at B2 and says is the WorkNo = ABC123? answer FALSE, Down
to
B3 and is the WorkNo = ABC123? answer FALSE, Down to B4 and is the
WorkNo
=
ABC123? answer TRUE and so on.

The third piece does not have a condition in it, so each entry in the
range
stays as it is, ie C2 = 6, C3 = 2, C4 = 4 and so on.

Now having done all that, Excel has created in it's memory a table that
looks like this:-

A B C
1 Name WorkNo Hours
2 FALSE FALSE 6
3 FALSE FALSE 2
4 TRUE TRUE 4
5 TRUE TRUE 9
6 FALSE FALSE 7
7 TRUE FALSE 6
8 TRUE FALSE 6
9 TRUE FALSE 6

but in Excel, TRUE evaluates to 1, whilst FALSE evaluates to 0, so the
table
really looks like this to Excel:-

A B C
1 Name WorkNo Hours
2 0 0 6
3 0 0 2
4 1 1 4
5 1 1 9
6 0 0 7
7 1 0 6
8 1 0 6
9 1 0 6

Now Excel uses the * signs in the formula which are really just
multiplication signs to decide what to do with each of the values, so
the
formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 ))

means multiply the first column by the second column and then muultiply
that
by the third column, ie


A B C
1 Name WorkNo Hours
2 0 * 0 * 6 = 0
3 0 * 0 * 2 = 0
4 1 * 1 * 4 = 4 <<<<<
5 1 * 1 * 9 = 9 <<<<<
6 0 * 0 * 7 = 0
7 1 * 0 * 6 = 0
8 1 * 0 * 6 = 0
9 1 * 0 * 6 = 0

As you can see, because of the 1s and 0s, the only bits of the equation
that
do not equal 0 are those that satisfy the criteria, which are rows 4 and
5,
and if you add them together (which the formula does for you) you will
get
13. This means that you can bill the customer for 13 hours of work that
John has spent on project ABC123.

I have only listed 3 columns but you could have more if you wanted.


Hope this helps, but by all means post back if you need any more
explanation.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


------------------------------*------------------------------*------------
----
It's easier to beg forgiveness than ask permission :-)

------------------------------*------------------------------*------------
----


"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

Firstly, try not to change the post title, as it plays havoc with archive
grouping of the threads :-)

As to your data, i would not use SUMPRODUCT for multiple totals as you need,
i would almost always use a Pivot table. That having been said, it may well
be that Data / Subtotals does what you need. As long as your data is sorted
on the field that you want to subtotal by, Data / Subtotlas is a great tool,
and allows you to summarise or detail your data at the click of a button
(namely the little numbers that appear top left on your sheet).

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

"Epinn" wrote in message
...
I am still thinking how I can have subtotals for each person without any
"hardcoding." For simplicity, let's just drop subtotals for workno for
now.
May be I can use advanced filter and make a separate list of names.
That's
it. Advanced filter (unique records only) + SUMPRODUCT. (I only got it
while typing this.) But it won't be the sixth method, still one of the
five
methods. By the way, I didn't check the list of the five methods. All of
a
sudden, the light bulb just .......

Would you please confirm that I am on the right track. Do I just put the
formula (taking out "John," workno check etc.) in one cell beside the
first
name of the unique name list and then double click on the handle to copy
it
down the list of names? I think I can experiment too after I have given
my
brain a rest.

I don't want to torture my brain right now by throwing in workno. But I
do
welcome your comments. May be I should forget about SUMPRODUCT and just
go
with PivotTable which is more dynamic. I think SUMPRODUCT by itself can
be
dynamic in generating one total, but to combine it with advanced filter it
is probably not worth it.

Thank you for reading when I think aloud. Please feel free to educate me.

Epinn

"Epinn" wrote in message
...
Ken,

I can't thank you enough. You are such a good teacher. I love details
and
I don't think your illustration can be any more detailed than this. You
really opened my eyes. The example you gave was different from the Excel
help, from the samples I listed in my previous post, from JE's formula
(not
entirely though). I didn't know that SUMPRODUCT could actually be used
to
filter data and we didn't even have to sort first. Wow! So dynamic.

I will check the links posted by Bernard. Thanks, Bernard. I have a
feeling that **eventually** I will figure out how to create SUMPRODUCT
formulas. That may be easier than looking at a SUMPRODUCT formula and
determining whether it is addition or multiplication. I am referring to
the
sample formulas that I posted in my previous post. Any comments?

When I learn, I try to look at the big picture. I have been wondering if
SUMPRODUCT() can be included in the following link as the sixth method.

http://office.microsoft.com/en-us/as...366211033.aspx

I doubt it because it only gives one grand total instead of various
subtotals. Please correct me if I am wrong. I see that I can use
SUMPRODUCT() to verify the grand total generated by any of the five
methods.

Thanks again. Hopefully, I can learn from you often in the future. Good
teachers are precious and few.

I may post back if I have any questions after checking out the links.

Cheers,

Epinn

"Ken Wright" wrote in message
...
As per Bernard's reply, Bob's page should hopefully cover it for you,

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

but if you just want a basic explanation of how it works then try
this:-

Imagine a range of data A1:C9 with Row 1 being titles

A B C
1 Name WorkNo Hours
2 Jim CDE456 6
3 Jim CDE456 2
4 John ABC123 4
5 John ABC123 9
6 Jim BCD444 7
7 John BCD444 6
8 John BCD444 6
9 John BCD444 6

The name bit is obvious, the workno or workpackage is a charge number
that
an employee would have been given to book the time he works to, whilst
working on a specific project, and the hours are obviously the hours he
actually worked on that project. The reason you would have more than
one
entry for each person is that these might represent different days.
The
business need is to total all the hours for each person by WorkNo and
then
add them all up so you can charge the customer for the right amount of
hours
spent on his project.

The following formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 )) evaluates to the
following:-

The system first looks at all the entries in Col A to work out the
(A2:A9="John") bit, and evaluates whether or not the statement is TRUE
or
FALSE. ie it goes down and looks at A2 and says is the name = John?
answer
FALSE, Down to A3 and is the name = John? answer FALSE, Down to A4 and
is
the name = John? answer TRUE and so on.

Then it does the same for the second statement (B2:B9="ABC123) ie it
goes
down and looks at B2 and says is the WorkNo = ABC123? answer FALSE,
Down
to
B3 and is the WorkNo = ABC123? answer FALSE, Down to B4 and is the
WorkNo
=
ABC123? answer TRUE and so on.

The third piece does not have a condition in it, so each entry in the
range
stays as it is, ie C2 = 6, C3 = 2, C4 = 4 and so on.

Now having done all that, Excel has created in it's memory a table that
looks like this:-

A B C
1 Name WorkNo Hours
2 FALSE FALSE 6
3 FALSE FALSE 2
4 TRUE TRUE 4
5 TRUE TRUE 9
6 FALSE FALSE 7
7 TRUE FALSE 6
8 TRUE FALSE 6
9 TRUE FALSE 6

but in Excel, TRUE evaluates to 1, whilst FALSE evaluates to 0, so the
table
really looks like this to Excel:-

A B C
1 Name WorkNo Hours
2 0 0 6
3 0 0 2
4 1 1 4
5 1 1 9
6 0 0 7
7 1 0 6
8 1 0 6
9 1 0 6

Now Excel uses the * signs in the formula which are really just
multiplication signs to decide what to do with each of the values, so
the
formula

=SUMPRODUCT((A2:A9="John")*(B2:B9="ABC123")*(C2:C9 ))

means multiply the first column by the second column and then muultiply
that
by the third column, ie


A B C
1 Name WorkNo Hours
2 0 * 0 * 6 = 0
3 0 * 0 * 2 = 0
4 1 * 1 * 4 = 4 <<<<<
5 1 * 1 * 9 = 9 <<<<<
6 0 * 0 * 7 = 0
7 1 * 0 * 6 = 0
8 1 * 0 * 6 = 0
9 1 * 0 * 6 = 0

As you can see, because of the 1s and 0s, the only bits of the equation
that
do not equal 0 are those that satisfy the criteria, which are rows 4
and
5,
and if you add them together (which the formula does for you) you will
get
13. This means that you can bill the customer for 13 hours of work
that
John has spent on project ABC123.

I have only listed 3 columns but you could have more if you wanted.


Hope this helps, but by all means post back if you need any more
explanation.


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


------------------------------*------------------------------*------------
----
It's easier to beg forgiveness than ask permission :-)

------------------------------*------------------------------*------------
----


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

Bob,

Epinn is not my real name. I had a cyber name with "e" as initial; you
know, same as the "e" in e-commerce, e-mail etc. Sorry, don't torture your
brain. It's all made up. Guess what, one MVP said that she loved my name,
Epinn. Not sure if she thought that it was a real name. Can't remember if
I explained it to her. By the way, I don't even know how to pronounce
Epinn. E-Pinn? or Ep-Pin? Like the "," or "*", suit yourself. :)

Hurray! I can choose what I want and "*" for now. But I have to tell you
this. After I sent out my last post, the light bulb was ____ and was
beginning to really see why you insist ",". It is the "right" thing and you
want to be exact. I want to be precise too and I may even help you
"recruit" in the future. <g Right now, I just want things simple and will
stick with "*".

No Epinn, that is not what I am saying. I am saying, don't even try to
get
the help changed. Help is correct.....

That was *exactly* what I was paraphrasing.

I wrote: 'No, no, absolutely not means NOT to change "," to "*" in the
original syntax?'

Original syntax = the syntax in Help

In other words, do NOT change the syntax in Help from "," to "*".

Okay, glad I am all clear. This is what I have decided for now. Don't
change the syntax in Help; use "*" and not "--" for everything else (e.g.
conditional test) regardless of whether there are other "*".
I am happy to tell you that I feel better today than yesterday - one step
forward. Thanks to you.

Should give this a break for at least the rest of Sept. 06. You probably
have gone to bed while I am typing this. Sweet dreams!

Epinn (Electronic Pinn)

"Bob Phillips" wrote in message
...


"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

No it isn't such a case, you can use * but as there is only one element in
this SP, you need to * against something, so the simplest is to multiply by
1

=SUMPRODUCT((MATCH(TRUE,$A1:$D1<"",0))*1)<COLUMN (A1)

-- is easier here as you don't need the seemingly redundant 1.

The formula is very simple, it just checks if the first data column is not
the column you are in, and blacks it if so.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
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