Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |