0

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)! ;)

1

Excel formulas handle the special error values of #N/A and #DIV/0 differently than other (real) values. Depending on the formulas you use, they get returned as the result no matter where they show up. So, your HLOOKUP is returning #N/A when it can't find the value, and even though you are trying to skip evaluating the HLOOKUP in the IF statement and return just "", Excel is still evaluating it and returning #N/A as soon as it's evaluated.

Also, because you're working with values and not text, your return value should be 0 and not "".

Instead of the outer IF statement in each SUM, you should use IFNA as follows:

IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0)

The IFNA formula returns the value in the first parameter if it actually works and produces a value, and returns a "" if it doesn't work.

The provided link has a sample worksheet to show how it works with a VLOOKUP, so you can test the formula out. Based on the sample there, this is the exact case that you would want to use IFNA.

So, if I'm reading what you are attempting to do correctly, the entire formula should be:

=SUM(IFNA(HLOOKUP(AB9,$B$9:$E$10,2,FALSE),0),IFNA(HLOOKUP(AB9,$F$9:$I$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$J$9:$M$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$N$9:$Q$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$R$9:$U$10,2,FALSE),""),IFNA(HLOOKUP(AB9,$V$9:$Y$10,
  • Thanks cathoo, it sounds as if I need to explore other functions! A colleague has a different solution to my problem. I'll expand on his answer in my own answer to the question. – HaydnWVN Aug 27 '14 at 15:56
  • IFNA did fix my problem, so it would be the ideal solution to my formula. But I have now changed that formula to do the task much more tidily (see other answers). – HaydnWVN Aug 28 '14 at 9:08
0

A colleague and I have come up with a better solution than nesting all the IF's together.

We came up with =SUMIF($B7:$AI7,AB9,$B8:$AI8) which works much better as no need for the multiple nested IF's.

I've expanded it further allowing for a default department and to include S (Sick) and H (Holiday) time which adds to the total hours for the default department.

=SUMIF($B7:$AI7,AL7,$B8:$AI8)+IF($AJ7=AL7,SUMIF($B7:$AI7,"S",$B8:$AI8)+SUMIF($B7:$AI7,"H",$B7:$AI7),0)

I'm going further as will include hourly rates and overtime next. Wish me luck!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.