Hi,

I've a lookup table with 5 columns.
column A is the criteria for searching/lookup and column B-E are
numerical Data.

e.g.
Col A Col B Col C Col D Col E
Name Result1 Result2 Result3 Result4
Amy 10 20 30 40
Ben 11 22 33 44

If I'd like to know the total of mixed results (such as Result1+2,
Result 1+2+3),it's not possible for sumif to do a sum range in array
e.g.
=sumif(A:A,"Amy",B:C)
=sumif(A:A,"Amy",B:D)
don't work.

However, writing nested formula like:
=sum(sumif(A:A,"Amy",B:B),sumif(A:A,"Amy",C:C),sum if(A:A,"Amy",D:D),sumif(A:A,"Amy",E:E))
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.

TRY:

=SUM(IF($A$2:$A$5=$A2,$B$2:$C$5))

$A2="Amy"

Entered as an array formula with Ctrl+Shift+Enter

HTH

" wrote:

Hi,

I've a lookup table with 5 columns.
column A is the criteria for searching/lookup and column B-E are
numerical Data.

e.g.
Col A Col B Col C Col D Col E
Name Result1 Result2 Result3 Result4
Amy 10 20 30 40
Ben 11 22 33 44

If I'd like to know the total of mixed results (such as Result1+2,
Result 1+2+3),it's not possible for sumif to do a sum range in array
e.g.
=sumif(A:A,"Amy",B:C)
=sumif(A:A,"Amy",B:D)
don't work.

However, writing nested formula like:
=sum(sumif(A:A,"Amy",B:B),sumif(A:A,"Amy",C:C),sum if(A:A,"Amy",D:D),sumif(A:A,"Amy",E:E))
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.

It works!
Thank you very much!

Toppers wrote:
TRY:

=SUM(IF($A$2:$A$5=$A2,$B$2:$C$5))

$A2="Amy"

Entered as an array formula with Ctrl+Shift+Enter

HTH

" wrote:

Hi,

I've a lookup table with 5 columns.
column A is the criteria for searching/lookup and column B-E are
numerical Data.

e.g.
Col A Col B Col C Col D Col E
Name Result1 Result2 Result3 Result4
Amy 10 20 30 40
Ben 11 22 33 44

If I'd like to know the total of mixed results (such as Result1+2,
Result 1+2+3),it's not possible for sumif to do a sum range in array
e.g.
=sumif(A:A,"Amy",B:C)
=sumif(A:A,"Amy",B:D)
don't work.

However, writing nested formula like:
=sum(sumif(A:A,"Amy",B:B),sumif(A:A,"Amy",C:C),sum if(A:A,"Amy",D:D),sumif(A:A,"Amy",E:E))
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.