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?

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

<TBODY>
</TBODY>

 

[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