+ Reply to Thread
Results 1 to 11 of 11

Sumproduct - multiple conditions

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    124

    Sumproduct - multiple conditions

    Hi

    i've tried searching a bit but i'm still new with SUMPRODUCT and not sure how to find a solution to this.

    Attached is a workbook showing what i'm trying to achieve.

    I am currently using VLOOKUP to pull info from an EXTERNAL workbook. 5 account numbers are listed on separate lines, one vlookup for each line, and the result is summed to give a total.

    At this stage, I am only able to write the sumproduct on individual lines to achieve the same effect as a sumif. I could combine these formulas into one long sumproduct but that would be quite tedious.

    I want to know if there is a simple SUMPRODUCT formula that can be written where i simply specify the full list of accounts, data to be summed, and then highlight all required accounts at once.

    i am reluctant to write out one very long sumproduct formula specifying each account number individually. My main aim is to specify the account numbers in combination (see red highlight in the attachment), and to have the answer in one cell.

    Thanks in advance!

    In the workbook
    External sheet - column A account number, column D is the value i want to pull
    Lookup - column A account number
    Attached Files Attached Files
    Last edited by rasonline; 06-04-2009 at 05:05 PM. Reason: SOLVED! YIPPEE

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct - multiple conditions

    Maybe you need Sumif?

    E.g

    =SUMIF('external sheet'!$A$1:$A$4613,A3,'external sheet'!$D$1:$D$4613)

    copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Sumproduct - multiple conditions

    =SUMPRODUCT(('external sheet'!$A$1:$A$4613=TRANSPOSE(lookup!A3:A8))*('external sheet'!D1:D4613))
    Entered with Ctrl+Shift+Enter

    alternatively

    =SUMPRODUCT(('external sheet'!$A$1:$A$4613={1400,1402,1403,1404,1405,1409})*('external sheet'!D1:D4613))

    confirmed with just enter
    Last edited by squiggler47; 06-04-2009 at 12:54 PM.
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct - multiple conditions

    I guess I am not having a good day understanding the problems

  5. #5
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Sumproduct - multiple conditions

    Quote Originally Posted by squiggler47 View Post
    =SUMPRODUCT(('external sheet'!$A$1:$A$4613={1400,1402,1403,1404,1405,1409})*('external sheet'!D1:D4613))

    confirmed with just enter
    This gives the answer i'm looking for, and in one cell. The only problem is that i'm trying to avoid having to type in the account numbers in the formula.

    In this solution we had to type the account numbers 1400,1402,1403,1404,1405,1409

    Is there no way to specify the account numbers as a range of cells?
    eg. something along the lines of:
    =SUMPRODUCT(('external sheet'!$A$1:$A$4613={a3:a8})*('external sheet'!D1:D4613))

    I am trying to write a standard sumproduct formula which i can then copy and paste to other sheets, which will then allow me the flexibility to change the account numbers without having to go into the sumproduct formula.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct - multiple conditions

    Try:

    =SUMPRODUCT((ISNUMBER(MATCH('external sheet'!$A$1:$A$4613,$A$3:$A$8,0))*('external sheet'!$D$1:$D$4613)))

  7. #7
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Sumproduct - multiple conditions

    The first formula I gave does!

    the second was an alternative!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct - multiple conditions

    ...and mine doesn't need CSE confirmation

  9. #9
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Sumproduct - multiple conditions

    Quote Originally Posted by NBVC View Post
    Try:

    =SUMPRODUCT((ISNUMBER(MATCH('external sheet'!$A$1:$A$4613,$A$3:$A$8,0))*('external sheet'!$D$1:$D$4613)))
    OOOOOOOOKKKK!! Now we're talking.

    Took me an hour to figure out the logic behind this but i do understand it now. Correct me if i'm wrong on this -
    MATCH - this matches the complete list of accounts to those that i specify, and returns a number. If no match, then returns an N/A
    ISNUMBER - where a number is returned, it will generate TRUE = 1

    So this gives the column of accuont numbers a list of 0's and 1's. 1's correspond the account numbers i specify.
    This is then multiplied out against the column with the values.

    ?

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct - multiple conditions

    Quote Originally Posted by rasonline View Post
    OOOOOOOOKKKK!! Now we're talking.

    Took me an hour to figure out the logic behind this but i do understand it now. Correct me if i'm wrong on this -
    MATCH - this matches the complete list of accounts to those that i specify, and returns a number. If no match, then returns an N/A
    ISNUMBER - where a number is returned, it will generate TRUE = 1

    So this gives the column of accuont numbers a list of 0's and 1's. 1's correspond the account numbers i specify.
    This is then multiplied out against the column with the values.

    ?
    Precisely!

    ...and those multiplied values are summed up.. to give final total.

  11. #11
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    124

    Re: Sumproduct - multiple conditions

    Quote Originally Posted by squiggler47 View Post
    The first formula I gave does!

    the second was an alternative!
    thanks for that. i just find that when working with array formulas that need ctrl+shift+enter that it becomes a little less flexible. but yes, your formula does indeed work.

    thanks for the help.

+ 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