1. ## 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. ## 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. ## Re: Formulas (2002)

PERFECT! Enough said.
Thanks so much

4. ## 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. ## Re: Formulas (2002)

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

6. ## 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>

7. ## 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. ## Re: Formulas (2002)

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

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