=(SUMPRODUCT((ISNUMBER(MATCH(Sheet1!$A$5:$A$21,$J$ 5:$J$6,0)))*(Sheet1!F36=Sh
eet1!$B$5:$B$21)*(Sheet1!$C$5:$M$21)))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"sanders" wrote in
message ...
Hi,
I Have this formula (kindy donated by this forum):
=(SUMPRODUCT((ISNUMBER(MATCH($J$5:$J$21,'Sheet1'!$ A$5:$A$21)))*(F36='sheet1'
!$B$5:$B$21)*('sheet1'!$C$5:$M$21)))
the problem is that it only gives the sum of the array for $C$5:$M$21
which matches the first value of $J$5:$J$21, and there is often more
than one match for the values in $J$5:$J$21 against $B$5:$B$21.
Is it possible to re write it so that it would give the sum of all the
instances in $B$5:$B$21?
Hope that all made sense
Thanks
--
sanders
------------------------------------------------------------------------
sanders's Profile:
http://www.excelforum.com/member.php...o&userid=36745
View this thread: http://www.excelforum.com/showthread...hreadid=572997