Results 1 to 9 of 9

Thread: Sumif

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Please see attached spreadsheet. What I would like to do is on worksheet 2, cell A1, sum all of volumes at a particular point, say A or B, and if the point is a receipt point, the sum should be positive, and if the point is a delivery point, it should be a negative sum. Any help?
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='771350' date='20-Apr-2009 09:18']Please see attached spreadsheet. What I would like to do is on worksheet 2, cell A1, sum all of volumes at a particular point, say A or B, and if the point is a receipt point, the sum should be positive, and if the point is a delivery point, it should be a negative sum. Any help?
    Thanks in advance.[/quote]Try:
    =SUMIF(Sheet1!B2:B15,"R",Sheet1!C2:C15)-SUMIF(Sheet1!B2:B15,"D",Sheet1!C2:C15)
    Regards
    Don

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='771350' date='20-Apr-2009 09:18']Please see attached spreadsheet. What I would like to do is on worksheet 2, cell A1, sum all of volumes at a particular point, say A or B, and if the point is a receipt point, the sum should be positive, and if the point is a delivery point, it should be a negative sum. Any help?
    Thanks in advance.[/quote]
    Alternatively, you might prefer the solution provided in the attached.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='771354' date='20-Apr-2009 08:50']Try:
    =SUMIF(Sheet1!B2:B15,"R",Sheet1!C2:C15)-SUMIF(Sheet1!B2:B15,"D",Sheet1!C2:C15)[/quote]


    Thanks, Steve. Unfortunately that is not what I am looking for. All of the original numbers are positive. What I want to do is where the point is a "Receipt" point, designated as "R", sum the designated points (A, B, C, etc.) and assign the sum a positive value. Where the point is a "Delivery" point, designated as "D", sum the designated points (A, B, C, etc.) and assign the sum a negative value. As an example, I would like the sum for Point B to show up as a -926, whereas for Point I, inasmuch as it is a Receipt Point, the sum should be a positive 373.
    Thanks again.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='771354' date='20-Apr-2009 08:50']Try:
    =SUMIF(Sheet1!B2:B15,"R",Sheet1!C2:C15)-SUMIF(Sheet1!B2:B15,"D",Sheet1!C2:C15)[/quote]


    WD,
    Works great! Mind stgepping through the formulae so that I might understand how it works?
    Thanks.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='771374' date='20-Apr-2009 11:20']WD,
    Works great! Mind stgepping through the formulae so that I might understand how it works?
    Thanks.[/quote]
    Do you wish an explanation to the single formula which I posted? Or the formulae found workbook which I subsequently posted?
    Regards
    Don

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='771388' date='20-Apr-2009 11:36']Do you wish an explanation to the single formula which I posted? Or the formulae found workbook which I subsequently posted?[/quote]


    The formula in the workbook

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='771390' date='20-Apr-2009 12:53']The formula in the workbook[/quote]
    Here is the formula from cell A1 broken down into three sections.

    a. =SUMIF(Sheet1!$A$2:$A$15,B1,Sheet1!$C$2:$C$15)*

    b. IF(ISERROR(MATCH(B1,Sheet1!$A$2:$A$15,0)),0,

    c. IF(INDEX(Sheet1!$B$2:$B$15,MATCH(B1,Sheet1!$A$2:$A $15,0))="R",1,-1))

    Section a checks each cell in the range A2:A15 and compares it against the entry in B1. If the comparison is TRUE, then the corresponding entry from C2:C15 is summed, giving us the absolute value of receipts or deliveries for the location identified by Sheet2 cell B1. This value is then multiplied by: 0, 1, or -1 as determined by sections b and c.

    Section b tests to ensure that we do not attempt to multiply section 'a' with an error caused by the location we are testing for not being found in the range of interest..

    Having assured ourselves that we will return a valid answer, section 'c' uses the MATCH function to find the location of the first occurrence of "A" in the range of interest. Using the INDEX function and the location returned by the MATCH function, the corresponding cell in column B returns the appropriate "R" or "D". If an "R" is returned, section 'a' is multiplied by 1; if a "D" is returned section 'a' is multiplied by minus 1.

    H.T.H.
    Regards
    Don

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='771407' date='20-Apr-2009 13:19']Here is the formula from cell A1 broken down into three sections.

    a. =SUMIF(Sheet1!$A$2:$A$15,B1,Sheet1!$C$2:$C$15)*

    b. IF(ISERROR(MATCH(B1,Sheet1!$A$2:$A$15,0)),0,

    c. IF(INDEX(Sheet1!$B$2:$B$15,MATCH(B1,Sheet1!$A$2:$A $15,0))="R",1,-1))

    Section a checks each cell in the range A2:A15 and compares it against the entry in B1. If the comparison is TRUE, then the corresponding entry from C2:C15 is summed, giving us the absolute value of receipts or deliveries for the location identified by Sheet2 cell B1. This value is then multiplied by: 0, 1, or -1 as determined by sections b and c.

    Section b tests to ensure that we do not attempt to multiply section 'a' with an error caused by the location we are testing for not being found in the range of interest..

    Having assured ourselves that we will return a valid answer, section 'c' uses the MATCH function to find the location of the first occurrence of "A" in the range of interest. Using the INDEX function and the location returned by the MATCH function, the corresponding cell in column B returns the appropriate "R" or "D". If an "R" is returned, section 'a' is multiplied by 1; if a "D" is returned section 'a' is multiplied by minus 1.

    H.T.H.[/quote]

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •