Index if conditional sum equals a value.
 

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.

Index if conditional sum equals a value.
 

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



"~L" wrote:

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.

Index if conditional sum equals a value.
 

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$5 0))

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

"Jason Morin" wrote in message
...
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



"~L" wrote:

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.