Sum of hours per tester
I would like to know how to add the total number of hours that each tester has, taking in consideration the following: a) The duration time for each course is in a cell by itself. b) Not all testers are taking all courses. c) Each tester will schedule an specific month as to when to take the test using the drop down table. Thanks in advance
Last edited by cescal; 02-21-2011 at 03:33 PM.
If you enter Tester 1 in B15 and Tester 2 in C15, then in B16 enter formula:
=SUMPRODUCT(($A$4:$A$5=B$15)*($B$4:$F$5=$A16)*($B$2:$F$2))
copied over to next column(s) and down
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
thanks for the help in advanced. I used the formula that you provided and it worked great. I used it to total the number of hours assigned to each tester. I took the same formula and tried to apply it next to it to now total the number of training hours taken but it only gives me a total of zero. thanks again
You have to play with the ranges to ensure you have the offset in ranges and that the ranges are of equal widths...
Try:
=SUMPRODUCT(($A$4:$A$6=B$22)*($C$4:$K$6=$A23)*($B$2:$J$2))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks