Hello,
I'm trying to get Excel to total hours for people based on "Type" of work and their name, with columns as follows:-
ColA ColB ColG ColH ColI
Type Name Start End TotalTime
D John Smith 08:15 17:45 09:30
O Betty Bloggs 09:05 12:30 03:25
T Fred Nerk 12:30 18:30 06:00
G Mildred Mould 08:30 20:15 11:45
A Ebenezer Rich 16:10 17:10 01:00
Obviously, dates and other information are included as well, and this table of information is in the HoursData worksheet.
The Time columns, above, are formatted to hh:mm.
In another sheet, called Tallies, I have a list of the different employees' names. Beside those, I have columns in which to record the grand total of the hours under each category they've worked, as well as a grand total of all hours. Those cells are formatted as [h]:mm, to allow for hundreds of hours over a year (or more).
I need to ask Excel to look in ColA for the Type, then in ColB for the Name, and then sum the hours in ColI that match those criteria.
I've got a result in a cell that is clearly incorrect.
The first gives me the total of 48:45 for TotalHours (this is accurate), but when I ask it for just the "D" Type hours, it gives 97:30, when it should be 09:00. (NB - This is double the TotalHours, so two identical sums are clearly happening in the same function).
There's obviously something wrong with the function I've created, as follows, but I can't work out how to fix it.
=IF(HoursData!$A$2:$A$1000="D",IF(HoursData!$B$2:$B$1000=Tallies!A4,SUM(HoursData!$I$2:$I$1000)))
Can anyone please help me with this?
Many thanks,
Excellent
Vic, Aus
I'm trying to get Excel to total hours for people based on "Type" of work and their name, with columns as follows:-
ColA ColB ColG ColH ColI
Type Name Start End TotalTime
D John Smith 08:15 17:45 09:30
O Betty Bloggs 09:05 12:30 03:25
T Fred Nerk 12:30 18:30 06:00
G Mildred Mould 08:30 20:15 11:45
A Ebenezer Rich 16:10 17:10 01:00
Obviously, dates and other information are included as well, and this table of information is in the HoursData worksheet.
The Time columns, above, are formatted to hh:mm.
In another sheet, called Tallies, I have a list of the different employees' names. Beside those, I have columns in which to record the grand total of the hours under each category they've worked, as well as a grand total of all hours. Those cells are formatted as [h]:mm, to allow for hundreds of hours over a year (or more).
I need to ask Excel to look in ColA for the Type, then in ColB for the Name, and then sum the hours in ColI that match those criteria.
I've got a result in a cell that is clearly incorrect.
The first gives me the total of 48:45 for TotalHours (this is accurate), but when I ask it for just the "D" Type hours, it gives 97:30, when it should be 09:00. (NB - This is double the TotalHours, so two identical sums are clearly happening in the same function).
There's obviously something wrong with the function I've created, as follows, but I can't work out how to fix it.
=IF(HoursData!$A$2:$A$1000="D",IF(HoursData!$B$2:$B$1000=Tallies!A4,SUM(HoursData!$I$2:$I$1000)))
Can anyone please help me with this?
Many thanks,
Excellent
Vic, Aus
