Sumproduct

S

sanders

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
 
R

robert111

COULD YOU PROVIDE AN EXAMPLE SPREADSHEET AND A DESCRIPTION OF what you
are trying to extract please, then all members can consider it.
 
B

Bob Phillips

=(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)
 
Top