Sumproduct

[Matt_Chelmsford]

I need a Sumproduct formula that (I think) encompasses an index – match formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
In range A2:A5000 I want to look up whatever S2 Says<o></o>
In Range B2:B5000 I want to look up whatever S3 says<o></o>
In Range C2:M2 I want to look up what ever S4 says.<o></o>
<o> </o>
In S5 I want to add up all the values that match the above three conditions.<o></o>
<o> </o>
So there are 3 criteria of which 2 are vertical and 1 that is horizontal. I have tried playing around with it but my efforts have been hopeless.<o></o>
<o> </o>
I would be grateful of any help given. <o></o>
<o> </o>
Regards<o></o>
<o> </o>
Matthew<o></o>

 

[MrKowz]

Try:

=SUMPRODUCT(--(A2:A5000=S2),--(B2:B5000=S3),INDEX(C2:M5000,,MATCH(S4,C2:M2)))

 

[barry houdini]

=SUMPRODUCT(--(A2:A5000=S2),--(B2:B5000=S3),INDEX(C2:M5000,,MATCH(S4,C2:M2)))

Assuming an exact match for S4 you'll need a third argument of zero in the MATCH function, i.e.

=SUMPRODUCT(--(A2:A5000=S2),--(B2:B5000=S3),INDEX(C2:M5000,0,MATCH(S4,C2:M2,0)))

That assumes that S4 exists only once in C2:M2. This version would accommodate multiple matches for S4 in that range:

=SUMPRODUCT((A3:A5000=S2)*(B3:B5000=S3)*(C2:M2=S4),C3:M5000)