Results 1 to 10 of 10

Thread: Formulas (2002)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas (2002)

    (Edited by HansV to display data in table)

    I need some formula help. How do I add up on Domestic flights that are only in period 2? I am thinking of an array. HELP

    <table border=1><td align=center>Periods</td><td align=center>Where</td><td align=center>Full Cost</td><td align=right>2</td><td>Domestic</td><td align=right>$123.00</td><td align=right>2</td><td>Transborder</td><td align=right>$214.00</td><td align=right>2</td><td>International</td><td align=right>$345.00</td><td align=right>1</td><td>Domestic</td><td align=right>$616.00</td><td align=right>1</td><td>Transborder</td><td align=right>$124.00</td><td align=right>2</td><td>International</td><td align=right>$214.00</td><td align=right>1</td><td>Domestic</td><td align=right>$521.00</td><td align=right>2</td><td>Transborder</td><td align=right>$52.00</td><td align=right>1</td><td>International</td><td align=right>$14.00</td></table>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (2002)

    Say that these data are in columns A, B and C, with field names in row 1. You can use either of these formulas:

    =SUM(IF((A2:A10=2)*(B2:B10="Domestic"),C2:C10))

    =SUMPRODUCT(N(A2:A10=2),N(B2:B10="Domestic"),C2:C1 0)

    The first of these is an array formula, i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter. The second one is a "normal" formula.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (2002)

    PERFECT! Enough said.
    Thanks so much

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (2002)

    I need to apply this formula to other sheets but the other sheet has Period 1 and Period 2 going down in the column. How do I work the formula or that? I can't seem to get it to work; am I missing something? the Values A2:A10 are coming from a different sheet as well.
    =SUMPRODUCT(N(A2:A10="Period 2"),N(B2:B10="Domestic"),C2:C10)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (2002)

    What is your problem? Does the formula return an error, or an incorrect value?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (2002)

    Do you mean that the ranges A2:A10, B2:B10, and C2:C10 are not on the same sheet as the cell containing the formula? If so, then you need something like this:

    <pre>=SUMPRODUCT(N('Different Sheet1'!A2:A10="Period 2"),N('Different Sheet2'!B2:B10="Domestic"),'Different Sheet3'!C2:C10)
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (2002)

    If I use a link to a different workbook the formulas does not work. Thanks for your help though, I found a work around.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formulas (2002)

    It is always nice if you can post the solution or workaround you found; other Loungers may benefit from it.

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas (2002)

    I had fun learning to use the SUMPRODUCT function on a worksheet with a similar two-way totaling requirement, but when I copied it to the lead-sheet, the references appeared but would not cooperate in formuals. What gives?
    My workaround was to leave the SUMPRODUCT on the original sheet and make a simple cell reference on the lead-sheet to it. Then it worked.
    I saw no reference to the N argument in the Excel Help for the SUMPRODUCT. Could I get further info on this? What does it signify?
    Thanks.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formulas (2002)

    From Excel Help:
    <hr>
    N
    Returns a value converted to a number.
    Syntax

    N(value)
    Value is the value you want converted. N converts values listed in the following table.

    If value is or refers to (N returns)
    A number: (That number)
    A date, in one of the built-in date formats available in Microsoft Excel: (The serial number of that date)
    TRUE: (1)
    Anything else: (0)
    <hr>

    Steve

Posting Permissions

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