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

"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

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

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