using left function in sumproduct

Earlier I posted an issue where I needed to establish an*average % across*multiple columns where multiple criteria was met and BSB kindly solved this for me. I stupidly forgot to mention that on the attached,*I will also need to establish an average for the whole of Lay team. I'm trying to achieve this using left in my formula but it's just not working.

*

Can anyone help by amending the working individual lay team averages to provide a solution in cell m14 for me

Try

=SUMPRODUCT((LEFT($C$3:$C$157,3)="LAY")*($D$3:$D$157=M$4)*($F$3:$J$157>=$M$3)*($F$3:$J$157<=$N$3),$E$3:$I$157)/SUMPRODUCT((LEFT($C$3:$C$157,3)="LAY")*($D$3:$D$157=M$4)*($F$3:$J$157>=$M$3)*($F$3:$J$157<=$N$3))

You can put this in M14 (changes in red):

=SUMPRODUCT((LEFT($C$3:$C$157,3)=LEFT($L14,3))*($D$3:$D$157=M$4)*($F$3:$J$157>=$M$3)*($F$3:$J$157<=$N$3),$E$3:$I$157)/SUMPRODUCT((LEFT($C$3:$C$157,3)=LEFT($L14,3))*($D$3:$D$157=M$4)*($F$3:$J$157>=$M$3)*($F$3:$J$157<=$N$3))

then copy into N14.

I've used the first three characters of the team names, but as you don't have any other teams which begin with "L" you could just omit the ,3 as the LEFT function will default to 1 in that case.

Hope this helps.

Pete

Thanks guys you've been a great help. Was trying everything to try and get left to work, I was close but no cigar. Thanks again