Count cells in another column until an adjacent column contains text

[dya]

Ok, this one is sorta complicated, and I haven't been able to find a good answer. Also, not wanting to use macros, since this is a schedule and will need to be sent out to many people (some with mac office 08).

So I have 3 columns, set up like this:

Time | Name | Total hours
.25 | |
.5 | |
1.5 | Sally | 2.25
1 | |
.75 | |
1.25 | |
1 | Joe | 4.00

So, I need formulas for that 3rd column (C). It should sum the values of the 1st column (A) on the same row and up to the row below the one where column B is not blank.
i.e. for the cell to the right of Joe (C8), it should add cells A8,A7,A6,A5 and not add A4 or before (or any below), because it has found a non blank cell in B4 where Sally's block ends.
(also, it should return blank if the adjacent B cell (name) is blank, but i can do that part)

Thank you so much!!!

 

[Aladin Akyurek]

In C2 enter and copy down:

=IF(B2="","",SUM(A2:INDEX($A$1:A1,1+MATCH(REPT("z",255),$B$1:B1))))

 

[dya]

That works very well, with 1 exception. If a person is only scheduled on one line, then the next person is scheduled on the next line, that next person's total hours come in as #REF!

 

[T. Valko]

Ok, this one is sorta complicated, and I haven't been able to find a good answer. Also, not wanting to use macros, since this is a schedule and will need to be sent out to many people (some with mac office 08).

So I have 3 columns, set up like this:

Time | Name | Total hours
.25 | |
.5 | |
1.5 | Sally | 2.25
1 | |
.75 | |
1.25 | |
1 | Joe | 4.00

So, I need formulas for that 3rd column (C). It should sum the values of the 1st column (A) on the same row and up to the row below the one where column B is not blank.
i.e. for the cell to the right of Joe (C8), it should add cells A8,A7,A6,A5 and not add A4 or before (or any below), because it has found a non blank cell in B4 where Sally's block ends.
(also, it should return blank if the adjacent B cell (name) is blank, but i can do that part)

Thank you so much!!!

Try this...

Book1
	A	B	C
1	Value	Name	Total
2	17
3	8
4	38
5	97	Sally	160
6	18
7	87	Joe	105
8	91	Biff	91
9	29
10	48
11	38
12	60
13	1
14	0	Karen	176
Sheet1

Formula entered in cell C2:

=IF(B2="","",SUM(A$2:A2)-SUM(C$1:C1))

Copy down as needed.

 

[dya]

That is so brilliantly simple. Thankyou!!!!!!

 

[T. Valko]

That is so brilliantly simple. Thankyou!!!!!!

You're welcome. Thanks for the feedback!