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