Product of 2 arrays based on criteria

I want to do a SUMPRODUCT of 2 arrays but only if the elements in the array
meet a criteria established by a thrid array. For example, consider the
three arrays, where the top array is names, the second array is salary and
the third is a percentage:
Amy Sam Tim Bob Tim Amy Bob
200 300 100 300 100 200 300
.4 .3 .2 .2 .1 .1 .5
I want to be able to end up with a sumproduct associated with each name.
Thus IF "Amy" SUMPRODUCT=100, IF "Tim" SUMPRODUCT=210 and so on.

Ben,

Assuming data in your example is in rows 1 to 3, colums A to G

=SUMPRODUCT(--($A$1:$G$1="Amy"),--($A$2:$G$2),--($A$3:$G$3))

HTH

"Ben010" wrote:

> I want to do a SUMPRODUCT of 2 arrays but only if the elements in the array
> meet a criteria established by a thrid array. For example, consider the
> three arrays, where the top array is names, the second array is salary and
> the third is a percentage:
> Amy Sam Tim Bob Tim Amy Bob
> 200 300 100 300 100 200 300
> .4 .3 .2 .2 .1 .1 .5
> I want to be able to end up with a sumproduct associated with each name.
> Thus IF "Amy" SUMPRODUCT=100, IF "Tim" SUMPRODUCT=210 and so on.
>

Toppers--
Thats excellent. One more for you: How do I sumproduct everyone except Amy
and Bob? Thats is, all people not equal to Amy or Bob?

Ben

"Toppers" wrote:

> Ben,
>
> Assuming data in your example is in rows 1 to 3, colums A to G
>
> =SUMPRODUCT(--($A$1:$G$1="Amy"),--($A$2:$G$2),--($A$3:$G$3))
>
> HTH
>
> "Ben010" wrote:
>
> > I want to do a SUMPRODUCT of 2 arrays but only if the elements in the array
> > meet a criteria established by a thrid array. For example, consider the
> > three arrays, where the top array is names, the second array is salary and
> > the third is a percentage:
> > Amy Sam Tim Bob Tim Amy Bob
> > 200 300 100 300 100 200 300
> > .4 .3 .2 .2 .1 .1 .5
> > I want to be able to end up with a sumproduct associated with each name.
> > Thus IF "Amy" SUMPRODUCT=100, IF "Tim" SUMPRODUCT=210 and so on.
> >

One way:

=SUMPRODUCT(--($A$1:$G$1<>"Amy"),--($A$1:$G$1<>"Bob"),--($A$2:$G$2),--($A$3:$G$3))

"Ben010" wrote:

> Toppers--
> Thats excellent. One more for you: How do I sumproduct everyone except Amy
> and Bob? Thats is, all people not equal to Amy or Bob?
>
> Ben
>
> "Toppers" wrote:
>
> > Ben,
> >
> > Assuming data in your example is in rows 1 to 3, colums A to G
> >
> > =SUMPRODUCT(--($A$1:$G$1="Amy"),--($A$2:$G$2),--($A$3:$G$3))
> >
> > HTH
> >
> > "Ben010" wrote:
> >
> > > I want to do a SUMPRODUCT of 2 arrays but only if the elements in the array
> > > meet a criteria established by a thrid array. For example, consider the
> > > three arrays, where the top array is names, the second array is salary and
> > > the third is a percentage:
> > > Amy Sam Tim Bob Tim Amy Bob
> > > 200 300 100 300 100 200 300
> > > .4 .3 .2 .2 .1 .1 .5
> > > I want to be able to end up with a sumproduct associated with each name.
> > > Thus IF "Amy" SUMPRODUCT=100, IF "Tim" SUMPRODUCT=210 and so on.
> > >

Outstanding. I appreciate your help.

"Toppers" wrote:

> One way:
>
> =SUMPRODUCT(--($A$1:$G$1<>"Amy"),--($A$1:$G$1<>"Bob"),--($A$2:$G$2),--($A$3:$G$3))
>
> "Ben010" wrote:
>
> > Toppers--
> > Thats excellent. One more for you: How do I sumproduct everyone except Amy
> > and Bob? Thats is, all people not equal to Amy or Bob?
> >
> > Ben
> >
> > "Toppers" wrote:
> >
> > > Ben,
> > >
> > > Assuming data in your example is in rows 1 to 3, colums A to G
> > >
> > > =SUMPRODUCT(--($A$1:$G$1="Amy"),--($A$2:$G$2),--($A$3:$G$3))
> > >
> > > HTH
> > >
> > > "Ben010" wrote:
> > >
> > > > I want to do a SUMPRODUCT of 2 arrays but only if the elements in the array
> > > > meet a criteria established by a thrid array. For example, consider the
> > > > three arrays, where the top array is names, the second array is salary and
> > > > the third is a percentage:
> > > > Amy Sam Tim Bob Tim Amy Bob
> > > > 200 300 100 300 100 200 300
> > > > .4 .3 .2 .2 .1 .1 .5
> > > > I want to be able to end up with a sumproduct associated with each name.
> > > > Thus IF "Amy" SUMPRODUCT=100, IF "Tim" SUMPRODUCT=210 and so on.
> > > >