SUM with nested IFs returning the wrong value

[cherryduck]

I'm trying to automate our overtime logging by importing a Sharepoint list into Excel and then automatically adding up all overtime hours done by a person in a day.

The formula I have:

=SUM(IF(Overtime!$E$1:$E$6003=$B19,IF(TEXT(Overtime!$H$1:$H$6003,"mmmm")=$B$3,IF(TEXT(Overtime!$H$1:$H$6003,"DD")=F$6,IF(YEAR(Overtime!$H$1:$H$6003)=$B$4,Overtime!$J$2:$J$6003)))))

Array entered of course.

In this case B19 contains the name of the person doing the overtime, B3 contains the current month, F6 contains the current day, B4 contains the current year, and the J column in the overtime sheet contains the number of hours worked.

This returns a number of hours but it returns the WRONG number of hours. In the cell where the above formula is placed, the matching name and date are in row 3 of the overtime sheet, but the hours returned is coming from J2, ie the cell in the row above. Oddly for row 2, the number of hours worked is being returned from J3 - ie the two are being swapped around.

This is a test with only one overtime event logged per person per day. In another test with people having multiple distinct entries for overtime on any specific date, it is sometimes correct, and sometimes wrong. I'm not sure where I'm gong wrong here.

 

[wideboydixon]

Last argument should be $J$1:$J$6003

WBD

 

[cherryduck]

Last argument should be $J$1:$J$6003

WBD

Oh ****, yeah that works. And to think I was most of the way there x) thanks!