Tim,

In cell E2, enter the formula

=IF(C2<=($D$2-SUM($E3:E$XXX)),C2,0)

Replace the XXX with a number higher than the highest row in your table, then copy this down to
match your data table.

HTH,
Bernie
MS Excel MVP


"tim m" wrote in message
...
Greetings,

I have the following scenario that I'm trying to wrap my head around.

(col A) I have a column with a unique Building identifying numbers.
(col B) I have a column with a building ranking # (ranks how important the
building is, this not necessarily unique, there will be several buildings
with the same rank #)
(col C) Number of maintenance hours for each building
(Cell D2) a building maintenance hours number calculated from other data.

In column E I need to add up the building maintenance hours (smallest to
largest) for the buildings up to the number in D2 and then list which
buildings #'s are invloved. But starting with the building with the lowest
ranking (3 being lowest, 1 being highest)

Sample data:

Bldg # Bldg rank Bldg maint hrs hours from calculation
1 1 15 100
2 1 30
3 2 35
4 2 50
5 3 20
6 3 10

So from the example above I want to add up bldg maint hours up to 100 and
list the bldg #'s with preference being given to the lower rankings 1st. so
for the example above it should pick bldg# 6, 5, 4, and then 1 (6,5,4 =80+1
=15) total 95