I have 4 columns
Column A contains a whole number
Column B contains a dollar amount
Colum C contains a dollar amount I refer to as a floor
Column D contains a dollar amount I refer to as a ceiling
Columns A & B have 10 rows
Columns C & D have 9 rows

I would like to sum the rows in Column A if the dollar amount in column B
falls between the floor and ceiling in columns C & D. I plan to copy this
formula to other areas of the worksheet that contain 9 rows that correspond
to the various floors and ceilings.

Does this make sense? I keep trying the sumif formula, but it doesn't seem
to be working for me.
Thanks

additional note: Please keep in mind that a dollar amount in column B may
fall between a floor and ceiling in columns C & D on a different row. For
example:

A B C D
10 $75 0 499
5 300 500 999
1 3500 1000 1499
15 1200 1500 1999
etc.

I would like a formula that sums all the rows in Column A where the dollar
amount falls between 0 and 499 (the answer would be 15). I will then have
another cell that asks for the sum of all the numbers in column A where the
dollar amount falls between 500 and 999 (the answer would be 0). I will have
9 cells with formulas that correspond to the nine levels of floors and
ceilings.
Thanks

"Tim Nealon" wrote:

I have 4 columns
Column A contains a whole number
Column B contains a dollar amount
Colum C contains a dollar amount I refer to as a floor
Column D contains a dollar amount I refer to as a ceiling
Columns A & B have 10 rows
Columns C & D have 9 rows

I would like to sum the rows in Column A if the dollar amount in column B
falls between the floor and ceiling in columns C & D. I plan to copy this
formula to other areas of the worksheet that contain 9 rows that correspond
to the various floors and ceilings.

Does this make sense? I keep trying the sumif formula, but it doesn't seem
to be working for me.
Thanks

Assuming your data starts in cell A2, and that the first set of test values
is found in C2 and D2, use this formula

=SUMPRODUCT($A$2:$A$11,--($B$2:$B$11C2),--($B$2:$B$11<D2))


"Tim Nealon" wrote:

additional note: Please keep in mind that a dollar amount in column B may
fall between a floor and ceiling in columns C & D on a different row. For
example:

A B C D
10 $75 0 499
5 300 500 999
1 3500 1000 1499
15 1200 1500 1999
etc.

I would like a formula that sums all the rows in Column A where the dollar
amount falls between 0 and 499 (the answer would be 15). I will then have
another cell that asks for the sum of all the numbers in column A where the
dollar amount falls between 500 and 999 (the answer would be 0). I will have
9 cells with formulas that correspond to the nine levels of floors and
ceilings.
Thanks

"Tim Nealon" wrote:

I have 4 columns
Column A contains a whole number
Column B contains a dollar amount
Colum C contains a dollar amount I refer to as a floor
Column D contains a dollar amount I refer to as a ceiling
Columns A & B have 10 rows
Columns C & D have 9 rows

I would like to sum the rows in Column A if the dollar amount in column B
falls between the floor and ceiling in columns C & D. I plan to copy this
formula to other areas of the worksheet that contain 9 rows that correspond
to the various floors and ceilings.

Does this make sense? I keep trying the sumif formula, but it doesn't seem
to be working for me.
Thanks

What's the meaning of the leading double subtraction signs in the criteria and sum_range?
--
Chris Cowles
Gainesville, FL

"Duke Carey" wrote in message ...
Assuming your data starts in cell A2, and that the first set of test values
is found in C2 and D2, use this formula

=SUMPRODUCT($A$2:$A$11,--($B$2:$B$11C2),--($B$2:$B$11<D2))

See these:

http://xldynamic.com/source/xld.SUMPRODUCT.html


http://mcgimpsey.com/excel/formulae/doubleneg.html

Biff

"Chris Cowles" wrote in message
...
What's the meaning of the leading double subtraction signs in the criteria
and sum_range?
--
Chris Cowles
Gainesville, FL

"Duke Carey" wrote in message
...
Assuming your data starts in cell A2, and that the first set of test
values
is found in C2 and D2, use this formula

=SUMPRODUCT($A$2:$A$11,--($B$2:$B$11C2),--($B$2:$B$11<D2))

Tim,

Try:
Headers in row 1
Floor 1:
=SUMPRODUCT(($B$2:$B$11=C2)*($B$2:$B$11<C3)*($A$2 :$A$11))

Drag down on th fill handle for floors 2 - 8

Floor 9:
=SUMPRODUCT(($B$2:$B$11=C10)*($A$2:$A$11))

No need for column D

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Tim Nealon" wrote in message
...
additional note: Please keep in mind that a dollar amount in column B may
fall between a floor and ceiling in columns C & D on a different row.
For
example:

A B C D
10 $75 0 499
5 300 500 999
1 3500 1000 1499
15 1200 1500 1999
etc.

I would like a formula that sums all the rows in Column A where the dollar
amount falls between 0 and 499 (the answer would be 15). I will then have
another cell that asks for the sum of all the numbers in column A where
the
dollar amount falls between 500 and 999 (the answer would be 0). I will
have
9 cells with formulas that correspond to the nine levels of floors and
ceilings.
Thanks

"Tim Nealon" wrote:

I have 4 columns
Column A contains a whole number
Column B contains a dollar amount
Colum C contains a dollar amount I refer to as a floor
Column D contains a dollar amount I refer to as a ceiling
Columns A & B have 10 rows
Columns C & D have 9 rows

I would like to sum the rows in Column A if the dollar amount in column B
falls between the floor and ceiling in columns C & D. I plan to copy
this
formula to other areas of the worksheet that contain 9 rows that
correspond
to the various floors and ceilings.

Does this make sense? I keep trying the sumif formula, but it doesn't
seem
to be working for me.
Thanks