I see that Dave Peterson gave you the xldynamic link, which is very
comprehensive.
To augment that a bit, I'll describe the SUMPRODUCT functionality
as it applies to formula in this thread.
Let's see if this helps:
SUMPRODUCT was *designed* to multiply two or more same-size
ranges of cells and sum the products.
Example (in cells A1:B4):
Sale Rate
100 0.10
200 0.15
300 0.20
The commission would be the sum of each Sale x Rate.
+(100 x 0.10)
+(200 x 0.15)
+(300 x 0.20)
In this instance, the total happens to be 100.
The SUMPRODUCT representation of that could be:
=SUMPRODUCT(A2:A4,B2:B4)
But SUMPRODUCT has an alternate structure, too:
=SUMPRODUCT((A2:A4)*(B2:B4))
(Sometimes the second one works where the
first one fails and vice versa)
The function multiplies the
A2 x B2 calcs 10.....adds to the total
A3 x B3 calcs 30.....adds to the total
A4 x B4 calcs 60.....adds to the total
Commission = 100 (10+30+60)
*********************************
Before continuing...Let's talk a bit about Boolean Values.
That's a fancy term for True/False values.
This formula:
A1: =(5=5)
returns TRUE....5 DOES equal 5
and
This formula:
A2: =(5=2)
returns FALSE....5 does NOT equal 2
As such, TRUE and FALSE...are treated as WORDS by Excel.
B1: =A1....returns TRUE
and
B2: =A2....returns FALSE
BUT...When you apply an arithmetic operator (+, -, *, /)
to a Boolean Value Excel converts TRUE to 1 and FALSE to 0.
So...
B1: =A1*1.....returns 1, instead of TRUE.
B2: =A2*1.....returns 0, instead of FALSE.
Actually, multiplying by any number converts
TRUE to 1 and FALSE to 0.
So...this would happen, too:
B1: =A1*10.....returns 10.
B2: =A2*10.....returns 0.
**********************************
We'll exploit that feature in SUMPRODUCT.....
In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)
This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.
Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.
SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"
Experiment with simple examples until you get
comfortable with the way it works.
I hope that helps.
Post back if you have more questions.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)