can I concatenate ranges within SUMPRODUCT ?

Posted by Chris D on January 25, 2002 1:06 PM

Hi all,

I'm trying to use sumproduct within identical ranges on 2 different worksheets....

ie : the value of "Chris" and "PER" and "TGCD" in 2 different ranges in different worksheets

I tried the following formula, but it returns "0" :

=SUMPRODUCT(($A$2:$A$21 & [Book4]Sheet1!$A$2:$A$21 = "chris") * ($B$2:$B$21 & [Book4]Sheet1!$B$2:$B$21 = "PER") * ($C$2:$C$21 & [Book4]Sheet1!$C$2:$C$21 = "TGCD") , ($D$2:$D$21 & [Book4]Sheet1!$D$2:$D$21))

is range&range not allowed ?

(I know I could sumproduct each sheet on its own then add the two together, but was trying to do it all in one go)

many thanks
Chris

Posted by Aladin Akyurek on January 25, 2002 1:22 PM

Chris --

In A1 enter: 1
In A2 enter: 2
In A3 enter: =A1&A2

You'll see what I'm getting at.

Cheers,

Aladin

==========

Posted by Chris D on January 25, 2002 1:42 PM

:-( nope....

Here's my data :

first sheet

{"name","cat1","cat2","amount";"chris","PER", "TATE",10;"harry","FIN","TGBA",15;"bob","PER", "TGCD",20;"sue","FIN","TATE",25;"chris","PER", "TGBA",30;"harry","FIN","TGCD",35;"bob","PER", "TATE",40;"sue","FIN","TGBA",45;"chris","PER", "TGCD",50;"harry","FIN","TATE",55;"bob","PER", "TGBA",60;"sue","FIN","TGCD",65;"chris","PER", "TATE",70;"harry","FIN","TGBA",75;"bob","PER", "TGCD",80;"sue","FIN","TATE",85;"chris","PER", "TGCD",90;"harry","FIN","TGCD",95;"bob","PER", "TATE",100;"sue","FIN","TGBA",105}

book4 :

=SUMPRODUCT((($A$2:$A$21 & [Book4]Sheet1!$A$2:$A$21) = "chris") * (($B$2:$B$21&[Book4]Sheet1!$B$2:$B$21) = "PER") * (($C$2:$C$21 & [Book4]Sheet1!$C$2:$C$21) = "TGCD") , ($D$2:$D$21 & [Book4]Sheet1!$D$2:$D$21))

returns 0, I'd expect an answer of 180, where the values of "Chris" and "PER" and "TGCD" from both sheets add up to 180.....

am I doing it wrong ?

thanks Aladin
Chris

Posted by Aladin Akyurek on January 25, 2002 2:15 PM

> =SUMPRODUCT((($A$2:$A$21&[Book4]Sheet1!$A$2:$A$21) = "chris") * (($B$2:$B$21 & [Book4]Sheet1!$B$2:$B$21) = "PER") * (($C$2:$C$21 & [Book4]Sheet1!$C$2:$C$21) = "TGCD") , ($D$2:$D$21 & [Book4]Sheet1!$D$2:$D$21))

> returns 0, I'd expect an answer of 180, where the values of "Chris" and "PER" and "TGCD" from both sheets add up to 180.....

You meant 280 of course.

> am I doing it wrong ?

The formula above concats names and categories together thru & and SUMPRODUCT sees no "chris" but "chrischris", "harryharry", etc, no "PER" but "PERPER" etc., whence 0 -- nothing to sum.

As you said yourself, you need to compute the multiconditional sum per sheet in a cell of its own in one of the target sheets or in some other and total the results per sheet.

Aladin

=====

Posted by Chris D on January 26, 2002 12:09 AM

chrischris, PERPER, TGCDTGCD etc.... yes, I'd totally missed that, thanks Aladin !

O&O
:-)