Sumif (sum if question)

[Guest]

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

 

[Guest]

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

 

[Guest]

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$11>C2),--($B$2:$B$11<D2))

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Chris Cowles]

What's the meaning of the leading double subtraction signs in the criteria and sum_range?

 

[T. Valko]

See these:

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


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

Biff

What's the meaning of the leading double subtraction signs in the criteria
and sum_range?