Adding values across a row based on a corresponding column value

[Craig Deutsch]

This is probably simple, but I cannot figure it out. Question refers to
table below:

Column A Column B Column C Column D
=============================
Julie 600 200 300
Scott 100 200 400
Tom 132132 111 4000
Craig 100 1000 100

Question: I'm trying to help a colleague calculate the value across a row
for Columns B-D when a value in Column A matches the criteria. For example,
I want Excel to calculate the totals for Scott. The result should be 700.

The formula I'm using is this: =SUMIF(A2:A5,"Scott",B25). I know there's
something wrong with my Sum_range, but I cannot figure it out. Any help is
appreciated. Thank you.

 

[Bernard Liengme]

=SUMPRODUCT((A2:A5="scott")*(B2:B5+C2:C5+D25))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

best wishes

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A4="Scott"),B1:B4+C1:C4+D14)

 

[Teethless mama]

Try this:

=SUMPRODUCT((A2:A5="Scott")*B25)

 

[T. Valko]

If "Scott" appears only once in column A:

=SUM(INDEX(B1100,MATCH("Scott",A1:A100,0),0))

 

[Craig Deutsch]

Thanks to all who replied so promptly!

It looks like there are several ways to solve this problem, one of which is
to use SUMPRODUCT, and the other to use SUM and INDEX functions. For now I
went with SUMPRODUCT, which works effectively when the values in column A are
unique. So in my example, it's necessary to ensure that the names are unique.

This new SUMPRODUCT is evidently quite powerful. I didn't know it even
existed. Must be new to Office 2007.

 

[Gord Dibben]

Has been around for several versions prior to 2007.


Gord Dibben MS Excel MVP

 

[Ashish Mathur]

Hi,

You can also do the following. In column E, use a SUM() formula

Now in a blank cell, use the formula =sumif(A2:A5,"Scott",E2:E5).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

Hi,

The SUMIF won't work, first because the request is to sum 3 columns which
SUMIF does not support.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Yes there are many ways including

=SUMPRODUCT((A1:A4=A8)*(B1:B4+C1:C4+D14))
=LOOKUP(A8,A1:A4,B1:B4+C1:C4+D14)
=SUMPRODUCT(VLOOKUP(A8,A14,{2,3,4}))
=SUM(OFFSET($A$1,MATCH(A8,A1:A4,0)-1,1,,3))
(provided there is only one occurance of Scott)

and this shortest I have seen

=SUM(IF(A1:A4=A8,B14,0))

This last requires array entry.

By the way the oldest version I currently have installed is 2000, but to the
best of my recollection SUMPRODUCT was around in version 5 (about 1994) at
least.


If these help, please click the Yes button

Cheers,
Shane Devenshire