Product of 2 arrays based on criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
One way:

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