Help with SumIf Formula

[currymonster]

Hi I wonder if anyone can help as I’m not an excel expert and am stuck.
I’ve entered some dummy data into my workbook to test it but can’t figure out what’s wrong
I’m trying to use a SUMIFS formula but can’t get it to work and I can’t see where I’ve gone wrong so if anybody could help that would be great.
What I want it to do is provide a weekly total of hours & minutes spent on calls by worker on the Inbox Record Log worksheet from data recorded on the worksheet between A2:J68 using a SUMIFS.
I’m trying to get the data to appear next to the correct worker name from Cell N3 week33 for worker John to cell X7 week 43 for worker Jason and I want the formula to add all the time by each worker in column J for each week Colum A.

I’ve tried two different version of the formula, one with ranges (see range names below) and one without.
Range Names

WeekNo which references Colum A (Week No) on worksheet Index Record Log
TimeTaken which references Colum (Time Taken) on worksheet Index Record Log
Worker which references Colum I (Worker) on worksheet Index Record Log



The two version of the formula I’ve written but can’t get to work is on the in cells N3 and 03 worksheet.
Can anybody help?
Thanks in advance for taking the time to read and any response.

[Nabeel]

i am not sure about your results but may be!!

=TEXT(SUMPRODUCT(($M3=$I$2:$I$10)*(N$2=SUBSTITUTE($A$2:$A$10," ","")),$J$2:$J$10),"hh:mm:ss")
OR
=TEXT(SUMIFS($J$2:$J$10,$A$2:$A$10,"Week "&RIGHT(N$2,2),$I$2:$I$10,$M3),"hh mm ss")

Nabeel

[HansV]

1) The ranges referred to in SUMIFS have to be the same size.
2) The data table has "Week 33" etc., but the summary table has "Week33" etc.
3) To be able to fill the formula to the right and down, you have to use the correct combination of absolute and relative references.
4) The sum range must be the first argument of SUMIFS (this is different from SUMIF).
5) The formula in N3 cannot refer to N3.

In N3:

=SUMIFS(TimeTaken,$I$2:$I$68,$M3,$A$2:$A$68,N$2)

Format N3 as time, then fill to the right and down.
Remark: if the total time can be more than 24 hours, use the custom format [h]:mm or [h]:mm:ss