+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : I need to add 2 different items names up into this one box useing sumifs

  1. 03-01-2012, 11:40 AM #1
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    I need to add 2 different items names up into this one box useing sumifs

    Here is my formula, it works great but it only add up "Mixed Juice" when I enter that in. I would like it to add up these other apples when I put in goldens and gala, and etc. It is for inventory and so right now this cell adds up only mixed juice in room 5 that is Juice, so I need it to add up mixed juice and goldens that are in room 5 and juice into one number.

    =SUMIFS('Data Sheet'!F3:F8007, 'Data Sheet'!E3:E8007, "Mixed Juice", 'Data Sheet'!H3:H8007, "Juice", 'Data Sheet'!I3:I8007, "5")-'Data Sheet'!T61-SUMIFS('Data Sheet'!N3:N8007, 'Data Sheet'!L3:L8007, "Mixed Juice", 'Data Sheet'!Q3:Q8007, "5")+SUMIFS('Data Sheet'!M3:M8007, 'Data Sheet'!L3:L8007, "Mixed Juice", 'Data Sheet'!Q3:Q8007, "5")

    I tried this but it didn't work (just added goldens to it):

    =SUMIFS('Data Sheet'!F3:F8007, 'Data Sheet'!E3:E8007, "Mixed Juice", 'Data Sheet'!E3:E8007, "Goldens", 'Data Sheet'!H3:H8007, "Juice", 'Data Sheet'!I3:I8007, "5")-'Data Sheet'!T61-SUMIFS('Data Sheet'!N3:N8007, 'Data Sheet'!L3:L8007, "Mixed Juice", 'Data Sheet'!Q3:Q8007, "5")+SUMIFS('Data Sheet'!M3:M8007, 'Data Sheet'!L3:L8007, "Mixed Juice", 'Data Sheet'!Q3:Q8007, "5")

    Please help thanks.
    Last edited by srgtennis; 03-01-2012 at 01:35 PM.
    Reply With QuoteRegister To Reply
  2. 03-01-2012, 12:35 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need to add 2 different items names up into this one box useing sumifs

    Enclose the options in { } brackets as the criteria for the criteria_range, then enclose the SUMIFS function in a SUM function.

    e.g.

    =SUM(SUMIFS('Data Sheet'!F3:F8007,'Data Sheet'!E3:E8007,{"Mixed Juice","Goldens"},'Data Sheet'!H3:H8007,"Juice",'Data Sheet'!I3:I8007,"5")
    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.
    Reply With QuoteRegister To Reply
  3. 03-01-2012, 12:50 PM #3
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    then enclose the SUMIFS function in a SUM function.

    sorry what does that mean?
    Reply With QuoteRegister To Reply
  4. 03-01-2012, 12:57 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need to add 2 different items names up into this one box useing sumifs

    The SUMIFS becomes nested in a SUM function to sum the SUMIF results for each of the items in the { } brackets.
    Reply With QuoteRegister To Reply
  5. 03-01-2012, 01:02 PM #5
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    so all I have to do is add the brackets

    =SUMIFS('Data Sheet'!F3:F8007,'Data Sheet'!E3:E8007,{"Mixed Juice","Goldens"},'Data Sheet'!H3:H8007,"Juice",'Data Sheet'!I3:I8007,"5")-'Data Sheet'!U61-SUMIFS('Data Sheet'!O3:O8007,'Data Sheet'!M3:M8007,"Mixed Juice",'Data Sheet'!R3:R8007,"5")+SUMIFS('Data Sheet'!N3:N8007,'Data Sheet'!M3:M8007,"Mixed Juice",'Data Sheet'!R3:R8007,"5")
    Reply With QuoteRegister To Reply
  6. 03-01-2012, 01:04 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need to add 2 different items names up into this one box useing sumifs

    and the SUM()

    e.g.

    =SUM(SUMIFS('Data Sheet'!F3:F8007,'Data Sheet'!E3:E8007,{"Mixed Juice","Goldens"},'Data Sheet'!H3:H8007,"Juice",'Data Sheet'!I3:I8007,"5"))-'Data Sheet'!U61-SUMIFS('Data Sheet'!O3:O8007,'Data Sheet'!M3:M8007,"Mixed Juice",'Data Sheet'!R3:R8007,"5")+SUMIFS('Data Sheet'!N3:N8007,'Data Sheet'!M3:M8007,"Mixed Juice",'Data Sheet'!R3:R8007,"5")

    not sure if you need do similarly in the other SUMIFS in the formula....
    Reply With QuoteRegister To Reply
  7. 03-01-2012, 01:08 PM #7
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    I put in 20 for mixed juice and 20 for goldens so it should be 40. i am getting nothing, I tried to sum each on of the sumifs and that didn't work either.
    Reply With QuoteRegister To Reply
  8. 03-01-2012, 01:12 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: I need to add 2 different items names up into this one box useing sumifs

    What do you get with just this part?

    =SUM(SUMIFS('Data sheet'!F3:F8007,'Data sheet'!E3:E8007,{"Mixed Juice","Goldens"},'Data sheet'!H3:H8007,"Juice",'Data sheet'!I3:I8007,"5"))

    is it what you expect for those conditions?

    If not, check that the relevant cells containing "Mixed Juice" or "Goldens" have no extra spaces, etc...
    Reply With QuoteRegister To Reply
  9. 03-01-2012, 01:17 PM #9
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    it give me -1049 but as soon as I removed all the sums then it give me 20 because of the Mixed Juice but it doesn't read the other Goldens 20.
    Reply With QuoteRegister To Reply
  10. 03-01-2012, 01:19 PM #10
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    it worked, not sure what I fixed but it is giving me 40 awesome thanks
    Reply With QuoteRegister To Reply
  11. 03-01-2012, 01:20 PM #11
    srgtennis
    srgtennis is offline
    Forum Contributor
    Join Date
    02-02-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    255

    Re: I need to add 2 different items names up into this one box useing sumifs

    I have the sum only on the first part. thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1