Index if conditional sum equals a value.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several columns of data. Column A is names, column B is hours, column
C is dates.

I'm looking for a formula that returns the date when the sum of hours for a
person (from a list in the range G2:G8) reaches a certain point (the number
in E1).

Currently the list is sorted by date in the hope that it will simplify this
problem.
 
OK, I'm going to cheat a little here since I'm rusty. Assuming you have
headers for your columns in row 1, you can:

1) Sort the 3 columns,, first by name ascending, then by date ascending
2) Insert this formula into D2 and copy down as far as you need to:

=IF(A2=A1,B2+D1,B2)

3) Now next to your name list, starting in H2, insert this and press
ctrl+shift+enter:

=INDEX($C$2:$C$50,MIN(IF((G2=$A$2:$A$50)*($D$2:$D$50>=$E$1),ROW($A$2:$A$50)-1)))

and copy down. The formula will bomb out if a person's total hours never
reach the the point in E1. Change the "50" in the formula to the last row
number in your columns that contains data.

HTH
Jason
Atlanta, GA
 
Welcome back!

You don't need the Index bit:

=MIN(IF((G2=$A$2:$A$50)*($D$2:$D$50>=$E$1),C$2:C$50))

But, the op wasn't very clear about the hours condition. Is it the min that
is >= E1 or is it the max that <= E1?

Biff
 
Back
Top