+ Reply to Thread
Results 1 to 10 of 10

Sumif/sumproduct with a range as a criteria

  1. #1
    Registered User
    Join Date
    04-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Sumif/sumproduct with a range as a criteria

    Hi guys. I ave been fighting with this for a while and will be really appreciated your help.
    I need to get the sum amount IF
    1. Store number matches X
    2. Period matches Y
    3. Account matches a range I2:I4

    I cannot figure out how to use this range I2:I4 as a criteria for SUMIFS.
    From another side I found out how to use 3rd criteria =SUMPRODUCT(SUMIF(C:C,I2:I4,E:E)). But I don't know how I can extract an array from this formula to merge with other conditions.

    Please see the example in the attachment.forum example.xlsx

  2. #2
    Registered User
    Join Date
    03-19-2013
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Sumif/sumproduct with a range as a criteria

    How's this?

    forum example ah.xlsx

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sumif/sumproduct with a range as a criteria

    to get the total in one go
    =SUMPRODUCT((C2:C200=I2)+(C2:C200=I3)+(C2:C200=I4),--(B2:B200="store 1"),--(D2:D200=1.2012),E2:E200)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    04-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Sumif/sumproduct with a range as a criteria

    Thank you Sir. But is there a way to replace this part (C2:C200=I2)+(C2:C200=I3)+(C2:C200=I4) with something straight-forward referring to a cell range? I will need to add some accounts or delete them. So I would prefer to have it more alterable.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Sumif/sumproduct with a range as a criteria

    If you need to make this scalable - add/remove accounts, try this. make a small table, with the accounts you want in I2 downwards. Then in J2, copied down, use this...

    =IF(I2="","",SUMIFS($E$2:$E$14,$A$2:$A$14,12,$D$2:$D$14,1.2012,$C$2:$C$14,I2))
    you can then just add up the values in J
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,879

    Re: Sumif/sumproduct with a range as a criteria

    Or use a pivot table
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sumif/sumproduct with a range as a criteria

    perhaps use advanced filter and sub total instead

  8. #8
    Registered User
    Join Date
    04-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Sumif/sumproduct with a range as a criteria

    Sorry if I misled you. It will definitely work for one store. I have hundreds of them so I cannot make sub-sum for each account for each store. How can I get an array {1,0,0,0,1,0,0,1,....} from comparing C:C to J:J? If C:C match any of J:J
    Thank you

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Sumif/sumproduct with a range as a criteria

    You can just extend your original example using SUMIFS instead of SUMIF, e.g.

    =SUMPRODUCT(SUMIFS(E:E,C:C,I2:I4,B:B,"Store 1",D:D,"1.2012"))
    Audere est facere

  10. #10
    Registered User
    Join Date
    04-07-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Sumif/sumproduct with a range as a criteria

    That's it! Thank you very much. I really appreciate it.
    I just don't understand the logic of sumproduct(sumifs())

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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