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(HoursDat a!$I$2:$I$1000)))

Can anyone please help me with this?

Many thanks,

Excellent
Vic, Aus :confused:

Post a sample workbook

I found out how to post a file, but the Forum doesn't allow an .xls file to be posted. How do I create a .zip file? :confused:

Thanks,

Excellent

I found out how to post a file, but the Forum doesn't allow an .xls file to be posted. How do I create a .zip file? :confused:

Thanks,

Excellent

If you have WindowsXP, or Vista, you can just right-click on the file and select

SEND TO > COMPRESSED (Zipped) FOLDER

Yeah it is a bit stupid that you can post other file formats without compression but not .xls
I, personally, think that ALL files should be zipped, to reduce up/down loads eating into both bandwidth AND (more importantly) some peoples data cap's. Also some people still have to contend with dial-up.

Hi,

Normally if you want to calculate based on multiple criteria you need to use SUMPRODUCT

See http://xldynamic.com/source/xld.SUMPRODUCT.html.

But if you post a sheet we can help further.

Ed