"Ron Coderre" wrote in message
...
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)


Oh no! I didn't understand a word of that - and I thought I was reasonably
intelligent :-(