SUMPRODUCT of Multiple columns

[gldurand]

Hi Gang

I have been trying to figure out how to use this formula when i need to SUM multiple columns

=SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$U$1:$U$65000)

I want to ADD Column U as stated after criteria met, but also Clumn V and W


I copied the formula 3 times changing the SUM Range that works but is there an easier way

=SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$U$1:$U$65000)

+

SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$V$1:$V$65000)

+

SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$W$1:$W$65000)

Thanks for any assistance

 

[Haseeb Avarakkan]

Hello, Try

=SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$U$1:$U$65000+'Source Data'!$V$1:$V$65000+'Source Data'!$W$1:$W$65000)

 

[gldurand]

Sorry Haseeb, not working, any ideas

 

[Haseeb Avarakkan]

What did you get when try this? Any error or not accurate result? Col U, V & W should contains only numbers. If there is any text in these columns will give error.

 

[gldurand]

i got a VALUE Error, i will check the data, but it seems good

 

[gldurand]

i found the problem, I was including the Header row $1 in my formula which is of course text

thanks

 

[Aladin Akyurek]

Hi Gang

I have been trying to figure out how to use this formula when i need to SUM multiple columns

=SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$U$1:$U$65000)

I want to ADD Column U as stated after criteria met, but also Clumn V and W


I copied the formula 3 times changing the SUM Range that works but is there an easier way

=SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$U$1:$U$65000)

+

SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$V$1:$V$65000)

+

SUMPRODUCT(--('Source Data'!$BE$1:$BE$65000=$A$3),--ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,{"Connectivity - Legacy","Connectivity - IP"},0)),--('Source Data'!$D$1:$D$65000="Run Rate"),'Source Data'!$W$1:$W$65000)

Thanks for any assistance

Try not to mix vectors and matrices in a SumProduct formula. Better switch to:

Control+shift+enter, not just enter:

=SUM(
   IF('Source Data'!$BE$1:$BE$65000=$A$3,
   IF(ISNUMBER(MATCH('Source Data'!$M$1:$M$65000,
           {"Connectivity - Legacy","Connectivity - IP"},0)),
   IF('Source Data'!$D$1:$D$65000="Run Rate"),
     'Source Data'!$U$1:$W$65000))))