Sumproduct
 

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

Sumproduct
 

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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572997

Sumproduct
 

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