Sumproduct
Hi, I have a formula : =SUMPRODUCT((('Totals'!$A$5:$A$11=J5:J11)*('Totals '!$B$5:$M$11))) but it only returns the values for J5 J8 and J9, not 6, 7, 10 or 11, as you would expect it to do. I have checked and the values it is looking up are accurate (they work individually). Is this a problem with sumproduct or am I just being a bit dim? Thanks -- sanders ------------------------------------------------------------------------ sanders's Profile: http://www.excelforum.com/member.php...o&userid=36745 View this thread: http://www.excelforum.com/showthread...hreadid=572127 |
Sumproduct
"sanders" ha scritto nel messaggio ... Hi, I have a formula : =SUMPRODUCT((('Totals'!$A$5:$A$11=J5:J11)*('Totals '!$B$5:$M$11))) but it only returns the values for J5 J8 and J9, not 6, 7, 10 or 11, as you would expect it to do. I have checked and the values it is looking up are accurate (they work individually). Is this a problem with sumproduct or am I just being a bit dim? Thanks Hi Sanders, The SUMPRODUCT function needs arrays of the same numbers of elements, but you ar multiplying an array of 7 (7 rows * 1 column) elements ('Totals'!$A$5:$A$11=J5:J11) by an array of 84 (7 rows * 12 column) elements ('Totals'!$B$5:$M$11), so it's right that the result is not what you think... You have to fix the two arrray to have the same number of elements... Check the on line help. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Sumproduct
Thanks for the quick response. I tested out what you suggested and modified the formula to read: =SUMPRODUCT((('Totals'!$A$5:$A$11=J5:J11)*('Totals '!$B$5:$B$11))) So it is only looking at one column of 7 at a time. But it is still only adding together the corresponding values in the B column for J5, 9, 10 and 11. Any ideas why this would be happening? Thanks again Franz Verga Wrote: "sanders" ha scritto nel messaggio ... Hi, I have a formula : =SUMPRODUCT((('Totals'!$A$5:$A$11=J5:J11)*('Totals '!$B$5:$M$11))) but it only returns the values for J5 J8 and J9, not 6, 7, 10 or 11, as you would expect it to do. I have checked and the values it is looking up are accurate (they work individually). Is this a problem with sumproduct or am I just being a bit dim? Thanks Hi Sanders, The SUMPRODUCT function needs arrays of the same numbers of elements, but you ar multiplying an array of 7 (7 rows * 1 column) elements ('Totals'!$A$5:$A$11=J5:J11) by an array of 84 (7 rows * 12 column) elements ('Totals'!$B$5:$M$11), so it's right that the result is not what you think... You have to fix the two arrray to have the same number of elements... Check the on line help. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy -- sanders ------------------------------------------------------------------------ sanders's Profile: http://www.excelforum.com/member.php...o&userid=36745 View this thread: http://www.excelforum.com/showthread...hreadid=572127 |
Sumproduct
=SUMPRODUCT((ISNUMBER(MATCH(J5:J11,Totals!$A$5:$A$ 11,0)))*(Totals!$B$5:$M$11
)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sanders" wrote in message ... Hi, I have a formula : =SUMPRODUCT((('Totals'!$A$5:$A$11=J5:J11)*('Totals '!$B$5:$M$11))) but it only returns the values for J5 J8 and J9, not 6, 7, 10 or 11, as you would expect it to do. I have checked and the values it is looking up are accurate (they work individually). Is this a problem with sumproduct or am I just being a bit dim? Thanks -- sanders ------------------------------------------------------------------------ sanders's Profile: http://www.excelforum.com/member.php...o&userid=36745 View this thread: http://www.excelforum.com/showthread...hreadid=572127 |
Sumproduct
Brilliant! That works a treat. Thanks for all your help. -- sanders ------------------------------------------------------------------------ sanders's Profile: http://www.excelforum.com/member.php...o&userid=36745 View this thread: http://www.excelforum.com/showthread...hreadid=572127 |
All times are GMT +1. The time now is 06:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com