# Index if conditional sum equals a value.

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.

G

#### Guest

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

T

#### T. Valko

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