SumProduct Multiple Columns

[dcamos]

I have a number in column B, either 68, 623 or 711 and I want to sum columns D through O (the entire row) if column B matches that number above.

I tried sumproduct(--(B2:B20=A1),D2:O20) but that didn't work and I am not sure where to go from there.

 

[Dryver14]

in C2

=IF(B2=$A$1,Sum(D2:O20),0)

 

[dcamos]

I really don't see how that formula will help at all.

 

[Aladin Akyurek]

I have a number in column B, either 68, 623 or 711 and I want to sum columns D through O (the entire row) if column B matches that number above.

I tried sumproduct(--(B2:B20=A1),D2:O20) but that didn't work and I am not sure where to go from there.

Not clear whether you want to match a range against 68, 623, and 711 separately or together...

Separately...

Control+shift+enter, not just enter:

=SUM(IF($B$2:$B$20=A1,$D2:$O$20))

Together...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH($B$2:$B$20,A1:A3,0)),$D$2:$D$20))

where A1:A3 houses 68, 623, and 711.

 

[dcamos]

I didn't think to try sum(if after sumproduct wouldn't do what I wanted.

Thanks,

 

[Joneye]

If you can post some data that will help.

I tried sumproduct(--(B2:B20=A1),D2:O20) but that didn't work and I am not sure where to go from there.

try =sumproduct(--(B2:B20=A1),--(D2:O20))

But the above second part normally needs to look like this (D2:02=??))

 

[dcamos]

Joneye, I regularly use sumproduct the way it was written, the second part is the sum range, it works for me all the time that way.

 

[Joneye]

Ok, but if you can post a group of data i can look it,

 

[Aladin Akyurek]

I didn't think to try sum(if after sumproduct wouldn't do what I wanted.

Thanks,

SUMPRODUCT(--(A2:A10="X"),--(B2:B10="Y"))

and

SUMPRODUCT(A2:B10,F2:G10)

are OK, but when mixing vectors with matrices are needed,
SumProduct's native syntax cannot be observed. In such cases,
it is better to invoke more flexible SUM/IF formulas.

 

[barry houdini]

I think Aladin, has given you a robust solution....

The reason that this doesn't work....

=SUMPRODUCT(--(B2:B20=A1),D2:O20)

.....is because all the ranges need to be the same size. Excel help says this

"The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value"

There is a workaround using SUMPRODUCT, if you multiply the two ranges then SUMPRODUCT is, in fact, dealing with only one array, e.g.

=SUMPRODUCT((B2:B20=A1)*D2:O20)

but note that this will give you an error if there is any text in the sum range D2:O20