Sum up 2 or more columns under same SUMIF criteria

N

nic.meow

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),sumif(A:A,"Amy",D:D),sumif(A:A,"Amy",E:E))
will drive people crazy.

Is there anyway to perforum this addition?
Thank you.
 
T

Toppers

TRY:

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

$A2="Amy"

Entered as an array formula with Ctrl+Shift+Enter

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top