+ Reply to Thread
Results 1 to 13 of 13

Having Trouble with SumIf

  1. 03-03-2015, 09:16 AM #1
    SabbathXXL
    SabbathXXL is offline
    Registered User
    Join Date
    02-05-2015
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    27

    Having Trouble with SumIf

    I have been working on a new spreadsheet and just cannot seem to get my head around SumIf. I have two instances in my worksheet where I should be using it.

    Firstly, I have a long list of different parts. I have a couple columns for all these parts, one column is a sum of how many parts we've received from an order, and another column is a sum of how many parts were taken by workers. In the spreadsheet, I have descriptions of what I'm looking for these cells to be doing. I'm hoping it's one of those situations where I'm just over-thinking and the solution is way simpler than I've been trying, but I've had no luck getting it to work so far. Any help is appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-03-2015, 09:53 AM #2
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Having Trouble with SumIf

    There's no example of how your Totals Pulled within each worker tab is setup.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."
    Reply With QuoteRegister To Reply
  3. 03-03-2015, 10:11 AM #3
    SabbathXXL
    SabbathXXL is offline
    Registered User
    Join Date
    02-05-2015
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    27

    Re: Having Trouble with SumIf

    Sorry. Threw together a dummy worksheet to retain sensitive information, skipped over a sheet. The new spreadsheet has a sheet in it now called "Totals Pulled" which shows how I have it currently set up. It's a bit sloppy, and I'd like to instead of an IF formula there, have it actively look up the names of the parts in case any parts are added or removed and I don't have to do any extra work beyond that, but it works.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 03-03-2015, 10:28 AM #4
    stunn
    stunn is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Having Trouble with SumIf

    SUMIF wont' work over multiple sheets. Use this in 'Parts Overview'!D3

    =SUM('Worker 1'!$B3:$P3,'Worker 2'!$B3:$P3,'Worker 3'!$B3:$P3)

    and this in 'Parts Overview'!E3

    =-SUMPRODUCT(-('Parts Ordered'!$D$2:$P$2=Reference!$A$3),'Parts Ordered'!$D4:$P4)
    Steve D. a.k.a. Stephen Dunn
    Reply With QuoteRegister To Reply
  5. 03-03-2015, 10:51 AM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Having Trouble with SumIf

    Quote Originally Posted by stunn View Post
    SUMIF wont' work over multiple sheets. Use this in 'Parts Overview'!D3

    =SUM('Worker 1'!$B3:$P3,'Worker 2'!$B3:$P3,'Worker 3'!$B3:$P3)

    and this in 'Parts Overview'!E3

    =-SUMPRODUCT(-('Parts Ordered'!$D$2:$P$2=Reference!$A$3),'Parts Ordered'!$D4:$P4)
    I think the SUM formula could be simplified to:

    =SUM('Worker 1:Worker 3'!$B3:$P3)
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  6. 03-03-2015, 12:33 PM #6
    SabbathXXL
    SabbathXXL is offline
    Registered User
    Join Date
    02-05-2015
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    27

    Re: Having Trouble with SumIf

    Quote Originally Posted by stunn View Post
    SUMIF wont' work over multiple sheets. Use this in 'Parts Overview'!D3

    =SUM('Worker 1'!$B3:$P3,'Worker 2'!$B3:$P3,'Worker 3'!$B3:$P3)

    and this in 'Parts Overview'!E3

    =-SUMPRODUCT(-('Parts Ordered'!$D$2:$P$2=Reference!$A$3),'Parts Ordered'!$D4:$P4)
    This worked beautifully. Thank you.

    If I can ask, just so I have a little more understanding, when formulas begin with =xxxx(-( and =xxxx(--(, what exactly does that do? Or does anybody have any references I could use to look up for myself?
    Reply With QuoteRegister To Reply
  7. 03-03-2015, 12:38 PM #7
    stunn
    stunn is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Having Trouble with SumIf

    This is the quickest way to turn a boolean result from a comparison such as (x=y) into a numeric value: -1 if TRUE, 0 if FALSE, but because it then results in a negative value, an additional '-' in front of the SUMPRODUCT makes it positive.
    Reply With QuoteRegister To Reply
  8. 03-03-2015, 12:40 PM #8
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Having Trouble with SumIf

    Sorry for jumping in between
    "--" is used to convert true and false values into numbers 1 and 0...
    so if you have a sumproduct function like =sumproduct(A1:A5="abc") and A1=a, A2=abc, A3=dfg, A4=abc, A5=qwe
    this formula would give a {false,true,false,true,false} so if you want to convert these trues and falses into numbers then use this "--" like this
    =sumproduct(--(A1:A5)) this would give {0,1,0,1,0} all true are converted into 1 and falses to 0 and then they are added and results comes out to be 2..
    Hope this helps!!
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    Reply With QuoteRegister To Reply
  9. 03-03-2015, 12:48 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Having Trouble with SumIf

    I prefer to keep things together, so, although there is absolutely nothing wrong with your way...
    =-SUMPRODUCT(-('Parts Ordered'!$D$2:$P$2=Reference!$A$3),'Parts Ordered'!$D4:$P4)

    I prefer to modify that slightly to...
    =SUMPRODUCT(--('Parts Ordered'!$D$2:$P$2=Reference!$A$3),'Parts Ordered'!$D4:$P4)
    This way when you see -, you know that it is for that specific reference, and dont need to see if it part of a boolean conversion
    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
    Reply With QuoteRegister To Reply
  10. 03-03-2015, 12:58 PM #10
    stunn
    stunn is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Having Trouble with SumIf

    Each to their own.

    My reasoning goes like this: For each comparison in the array (in this case it's only 13 items, but it could be any amount) the boolean is being negated. In my case only 14 negations are made, in your case, there are 26. It may never really make an impact, even in large datasets, but I just can't bring myself to ignore that.
    Reply With QuoteRegister To Reply
  11. 03-03-2015, 12:59 PM #11
    SabbathXXL
    SabbathXXL is offline
    Registered User
    Join Date
    02-05-2015
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    27

    Re: Having Trouble with SumIf

    That helps a great deal. I appreciate all the help. You guys have been awesome.
    Reply With QuoteRegister To Reply
  12. 03-03-2015, 01:01 PM #12
    stunn
    stunn is offline
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Having Trouble with SumIf

    You're welcome, thanks for the feedback.
    Reply With QuoteRegister To Reply
  13. 03-03-2015, 01:17 PM #13
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Having Trouble with SumIf

    Quote Originally Posted by stunn View Post
    Each to their own.

    My reasoning goes like this: For each comparison in the array (in this case it's only 13 items, but it could be any amount) the boolean is being negated. In my case only 14 negations are made, in your case, there are 26. It may never really make an impact, even in large datasets, but I just can't bring myself to ignore that.
    That does make sense, and is a good way of looking at it, thanks for sharing
    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)

Similar Threads

  1. [SOLVED] Trouble with simple sumif
    By sipa in forum Excel General
    Replies: 6
    Last Post: 01-26-2015, 11:50 AM
  2. Trouble with SUMIF
    By hechtic in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-30-2014, 02:39 PM
  3. Trouble using =SUMIF
    By slope in forum Excel General
    Replies: 9
    Last Post: 06-11-2009, 04:28 AM
  4. Sumif Trouble
    By dreicer_Jarr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-20-2009, 12:57 PM
  5. Trouble with SUMIF
    By shandy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2007, 04:55 AM

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