Suming using 2 criteria

Sheet 1 has
A B
Bob Y
Dan
Pete
Ted Y
Mia Y
...

Sheet 2 has
A B C
Bob 10 Q
Dan 15 W
Pete 8 E
Ted 12 R
Mia 22 T
...

I want to sum(sheet2!B1:B100) where (sheet2!C="W") and (sheet1!B = "Y" where
(sheet2!A = sheet1!A))

I know it can be done but I can't figure out the formula construct.

Anyone?

One way:

=SUMPRODUCT(--(Sheet1!B1:B100="Y"), --(Sheet2!C1:C100="W"),
Sheet2!B1:B100)



In article <uyNN$euYFHA.3620@TK2MSFTNGP09.phx.gbl>,
"Hall" <hall@garp.org> wrote:

> Sheet 1 has
> A B
> Bob Y
> Dan
> Pete
> Ted Y
> Mia Y
> ..
>
> Sheet 2 has
> A B C
> Bob 10 Q
> Dan 15 W
> Pete 8 E
> Ted 12 R
> Mia 22 T
> ..
>
> I want to sum(sheet2!B1:B100) where (sheet2!C="W") and (sheet1!B = "Y" where
> (sheet2!A = sheet1!A))
>
> I know it can be done but I can't figure out the formula construct.
>
> Anyone?