I'm using Excel to search multiple days (my ranges) in a timesheet for hours worked (my HLOOKUP
reads the value below the dept number) by multiple members of staff for different departments.
I've come up with the following formula which works for the majority of my sheet, except if the lookup cell (AB9) value isn't found in the first range ($B$9:$E$10). For others it works (if the value isn't found in the later ranges it doesn't return a #N/A), only if the value isn't found in the first range.
=SUM(IF(COUNTIF($B$9:$E$10,AB9),HLOOKUP(AB9,$B$9:$E$10,2,FALSE),""))+(IF(COUNTIF($F$9:$I$10,AB9),HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""))+(IF(COUNTIF($J$9:$M$10,AB9),HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""))+(IF(COUNTIF($N$9:$Q$10,AB9),HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""))+(IF(COUNTIF($R$9:$U$10,AB9),HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""))+(IF(COUNTIF($V$9:$Y$10,AB9),HLOOKUP(AB9,$V$9:$Y$10,2,FALSE),""))
Can someone explain to me why this is failing and if I shouldn't be using COUNTIF for this, what else I should be using and why.
Ideally I would have my formula looking for unique numbers (departments) in the ranges, reporting them and then below listing the hours worked for each - but that is a little above my expertise (currently)! ;)