formula of sum and text

G

Gary

In column "A" (A1:A20) titled "Employees", there's recorded employee names
as work is done through a weeks time. There are ten different names that
are possible.



In column "B" there are empty cells (B1:B20) titled "Miles". When an
employee logs miles on a day it is registered in column "B" adjacent to
their name. There are only two employees per sheet that will have recorded
miles, although it is not always the same two.



In cell "C1" & "C2" there are empty cells titled "Employee Miles". We
manually record the two employee names that have miles on that particular
sheet in cells "C1" & "C2".



In cell "D1" & "D2" there are empty cells titled "Miles Total". We manually
count the miles each of the two employees have recorded in different cells
in column "B" through a weeks time and type the totals in cells "D1" & "D2".



In the cells in column "C" we wish to have the names automatically appear
based on if that particular name had any mileage recorded in column "B". In
the cells in column "D" we wish to have the totals automatically appear
based on the sum of miles in column "B" for that particular employee.



How may we automate entries in "C1" & "C2" and "D1" & "D2"?



Regards,

Gary
 
T

T. Valko

If I understand .....

...........A..........B
1......Tom.......10
2......Bob...........
3......Joe.........10
4......Lou...........
5......Tom.......10

You want Tom and Joe extracted and the total of their miles logged?

Enter this array** formula in C1:

=INDEX(A1:A5,MATCH(TRUE,B1:B5<>"",0))

Enter this array** formula in C2:

=INDEX(A1:A5,MATCH(1,(B1:B5<>"")*(A1:A5<>C1),0))

Enter this formula D1 and copy to D2:

=SUMIF(A$1:A$5,C1,B$1:B$5)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

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

Similar Threads

sum 1
Conditional calculations 4
Excel Formula Challenge... 9
Formula wanted 5
IF, AND & OR 3
Data Validation and drop-down list using IF, AND and what else? 8
Macro copies values in other workbook 1
Excel VBA - counting cells by colors and names to it in two different rows. 0

Top