Results 1 to 9 of 9
Thread: Sumif

20090420, 09:18 #1
 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.

20090420, 09:50 #2
 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='20Apr2009 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

20090420, 10:19 #3
 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='20Apr2009 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.Regards
Don

20090420, 10:21 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='wdwells' post='771354' date='20Apr2009 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.

20090420, 11:20 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='wdwells' post='771354' date='20Apr2009 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.

20090420, 12:36 #6
 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='20Apr2009 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

20090420, 12:53 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='wdwells' post='771388' date='20Apr2009 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

20090420, 14:19 #8
 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='20Apr2009 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

20090421, 07:39 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='wdwells' post='771407' date='20Apr2009 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!