Sumproduct-give the sum of all the instances

[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

[robert111]

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

[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)

"sanders" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>