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.
 
Ad

Advertisements

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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top