# Sum up 2 or more columns under same SUMIF criteria

1. ## Sum up 2 or more columns under same SUMIF criteria

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.

2. ## RE: Sum up 2 or more columns under same SUMIF criteria

TRY:

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

\$A2="Amy"

Entered as an array formula with Ctrl+Shift+Enter

HTH

"nic.meow@gmail.com" 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),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.
>
>

3. ## Re: Sum up 2 or more columns under same SUMIF criteria

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
>
> "nic.meow@gmail.com" 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),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.
> >
> >

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1