1. 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?

2. [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?
=SUMIF(Sheet1!B2:B15,"R",Sheet1!C2:C15)-SUMIF(Sheet1!B2:B15,"D",Sheet1!C2:C15)

3. [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?
Alternatively, you might prefer the solution provided in the attached.

4. [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. [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. [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?

7. [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. [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.

9. [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
•