Excel macro/function like VLOOKUP that sums eventual multiple matches

How can I create an Excel command (macro or function) that works like the
VLOOKUP function but sums eventual multiple matches?

Example:

Sheet1 - before execution:
DOG | WHITE
HOUSE | YELLOW
CAT | GRAY

Sheet2 - before execution:
HOUSE | 300
DOG | 780
DOG | 200
BALL | 560

Sheet1 - AFTER execution:
DOG | WHITE | 980
HOUSE | YELLOW | 300
CAT | GRAY |

=SUMIF(Sheet2!A1:A999,A1,Sheet2:B1:B999)

This formula is to be coded in Sheet1, cell C1.
Then copied down

HTH
--
AP

"Mary" <[email protected]> a écrit dans le message de news:
[email protected]...
> How can I create an Excel command (macro or function) that works like the
> VLOOKUP function but sums eventual multiple matches?
>
> Example:
>
> Sheet1 - before execution:
> DOG | WHITE
> HOUSE | YELLOW
> CAT | GRAY
>
> Sheet2 - before execution:
> HOUSE | 300
> DOG | 780
> DOG | 200
> BALL | 560
>
> Sheet1 - AFTER execution:
> DOG | WHITE | 980
> HOUSE | YELLOW | 300
> CAT | GRAY |
>
>

Take a look at the sumproduct formula. Here is a link that explains it...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Mary" wrote:

> How can I create an Excel command (macro or function) that works like the
> VLOOKUP function but sums eventual multiple matches?
>
> Example:
>
> Sheet1 - before execution:
> DOG | WHITE
> HOUSE | YELLOW
> CAT | GRAY
>
> Sheet2 - before execution:
> HOUSE | 300
> DOG | 780
> DOG | 200
> BALL | 560
>
> Sheet1 - AFTER execution:
> DOG | WHITE | 980
> HOUSE | YELLOW | 300
> CAT | GRAY |
>
>
>