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