Ignore Text in a Multi Sum Formula

[Dkendr02]

I am trying to calculate the total # of hours of operation using the formula below where each value is a time. If one of the values is text - "Closed" - i get a #VALUE! error. I tried turning it into a SUM formula but I get the same error.
What do i need to change in order to return a value that completes the calculation but ignores the text? Note that 'closed' could appear multiple times in any cell.

=((J44-I44)+(L44-K44)+(N44-M44)+(P44-O44)+(R44-Q44)+(T44-S44)+(V44-U44))

or

=((8am-4pm)+(8am-4pm)+(8am-4pm)+(8am-4pm)+(8am-4pm)+(8am-4pm)+(CLOSED-CLOSED))

 

[DanteAmor]

Try:

=((N(J44)-N(I44))+(N(L44)-N(K44))+(N(N44)-N(M44))+(N(P44)-N(O44))+(N(R44)-N(Q44))+(N(T44)-N(S44))+(N(V44)-N(U44)))

 

[Dkendr02]

=((N(J44)-N(I44))+(N(L44)-N(K44))+(N(N44)-N(M44))+(N(P44)-N(O44))+(N(R44)-N(Q44))+(N(T44)-N(S44))+(N(V44)-N(U44)))

This does return a value but it is overstated....the facility i am working on is open 10am-6pm Monday thru Saturday, closed on Sunday. The correct value for this should be 48 (8hrs X 6 days). When I use this formula, it returns the value "68".

 

[Dkendr02]

This does return a value but it is overstated....the facility i am working on is open 10am-6pm Monday thru Saturday, closed on Sunday. The correct value for this should be 48 (8hrs X 6 days). When I use this formula, it returns the value "68".
View attachment 86032

and Thank you for the assist Dante!

 

[DanteAmor]

The result of the N function is the serial number of the hours, now you must multiply it by 24 to have the total in hours:

Dante Amor
	A							H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
42									MONDAY		TUESDAY		WEDNESDAY		THURSDAY		FRIDAY		SATURDAY		SUNDAY
43								Hrs	Open	Close	Open	Close	Open	Close	Open	Close	Open	Close	Open	Close	Open	Close
44								48	10:00 a. m.	06:00 p. m.	10:00 a. m.	06:00 p. m.	10:00 a. m.	06:00 p. m.	10:00 a. m.	06:00 p. m.	10:00 a. m.	06:00 p. m.	10:00 a. m.	06:00 p. m.	CLOSED	CLOSED
Hoja2

Cell Formulas
Range	Formula
H44	H44	=((N(J44)-N(I44))+(N(L44)-N(K44))+(N(N44)-N(M44))+(N(P44)-N(O44))+(N(R44)-N(Q44))+(N(T44)-N(S44))+(N(V44)-N(U44)))*24

 

[Dkendr02]

=((N(J44)-N(I44))+(N(L44)-N(K44))+(N(N44)-N(M44))+(N(P44)-N(O44))+(N(R44)-N(Q44))+(N(T44)-N(S44))+(N(V44)-N(U44)))*24

I am still getting 68....are your "06:00 p.m." values being treated differently than mine?

 

[DanteAmor]

Your formula points to cells in row 44, in your image I only see row 41.
In my example the formula is for the cells of row 44 and the result is correct: 48 hours.

 

[Dkendr02]

AHHHHH.....I am a bonehead!!! THanks Dante - it does work as stated!