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