If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 news 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. |
Thread Tools | |
Display Modes | |
|
|