Complicated - from #value! to a blank & sum time

[SJNTAN]

Hello!
I have a pretty complicated spreadsheet to work on and I need some help.
The spreadsheet has lots of merged cells which I managed to resolved.

1. I tried to sum the total time but it just return as 0:00:00 - I have formatted the cells as [h]:mm:ss. Any ideas what is causing the return value as 0:00:00
2. For Prod Time (HR) for Staff - formula used - =SUM(IFERROR(VLOOKUP($AY14&$AX14,$C$14:$AI$50,20,0),""),IFERROR(VLOOKUP($AY14&$AX14,$C$14:$AI$50,23,0),""),IFERROR(VLOOKUP($AY14&$AX14,$C$14:$AI$50,27,0),""),IFERROR(VLOOKUP($AY14&$AX14,$C$14:$AI$50,30,0),""))
but return value for an absent staff kept showing #value!
And that causes the Total to be #value! too.
I tried using =SUMIF(BA14:BA60,"<>#N/A") but it just didn't work.
Any help from anybody please?

[TABLE="width: 254"]
<TBODY>[TR]
[TD]Staff Name</SPAN>
[/TD]
[TD]Staff Time (Hr)</SPAN>
[/TD]
[TD]Prod Time (Hr)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Report Total</SPAN>
[/TD]
[TD]0:00:00</SPAN>
[/TD]
[TD]#VALUE!</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff A</SPAN>
[/TD]
[TD]07:29:40</SPAN>
[/TD]
[TD]4:28:41</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff B</SPAN>
[/TD]
[TD]
[/TD]
[TD]#VALUE!</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff C</SPAN>
[/TD]
[TD]07:54:41</SPAN>
[/TD]
[TD]6:43:37</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff D</SPAN>
[/TD]
[TD]07:16:34</SPAN>
[/TD]
[TD]4:38:38</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff E</SPAN>
[/TD]
[TD]
[/TD]
[TD]#VALUE!</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff F</SPAN>
[/TD]
[TD]06:34:29</SPAN>
[/TD]
[TD]4:08:11</SPAN>
[/TD]
[/TR]
[TR]
[TD]Staff G</SPAN>
[/TD]
[TD]
[/TD]
[TD]#VALUE!</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Pup Denab]

HI SJNTAN
Welcome to the forum, have you tried replacing the N/A with #VALUE!, that should work, personally I would change the formula their have enter, so it didn't show an error value, as I'm fairly sure, that will be their next requirement for you
Regards
Pup

 

[SJNTAN]

Hi Pup,

Thank you. It works! I changed the formula to =SUMIF(BA14:BA60,"<>#value!") and got the time sum up.

Would you be able to help with the sum of staff time as well?

Once again thank you!

 

[Pup Denab]

HI SJNTAN
I would be tempted to use the same formula, as you don't know, if that error value will come up again, just on the staff column
Regards
Pup



Just glad I could help