Results 1 to 10 of 10
Thread: Formulas (2002)

20040520, 16:04 #1
 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>

20040520, 16:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040520, 18:22 #3
 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

20040520, 23:03 #4
 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)

20040520, 23:20 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formulas (2002)
What is your problem? Does the formula return an error, or an incorrect value?

20040521, 00:25 #6
 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

20040523, 13:43 #7
 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.

20040523, 19:39 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formulas (2002)
It is always nice if you can post the solution or workaround you found; other Loungers may benefit from it.

20040524, 17:51 #9
 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 twoway totaling requirement, but when I copied it to the leadsheet, 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 leadsheet 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.

20040524, 18:05 #10
 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 builtin date formats available in Microsoft Excel: (The serial number of that date)
TRUE: (1)
Anything else: (0)
<hr>
Steve