+ Reply to Thread
Results 1 to 3 of 3

Sum up 2 or more columns under same SUMIF criteria

  1. #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. #2
    Toppers
    Guest

    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

    "[email protected]" 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. #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
    >
    > "[email protected]" 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.
    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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