Hi: I read an old thread about SUM or SUMPRODUCTS like doing =SUM((A1:D1)*1) etc.

I am adding from non-array cells in an excel worksheet using simple =A1+B1+C1+D1.It works when A1 to D1 are all numbers but when one of the cell eg B1 is denoted as a text "N" (to represent None Attendance for the D1 event), I get an error as attached snapshot.

If possible, how would one use the SUM or SUMPRODUCTS formula versions to accept the N events without triggering an error in in the answer [this case?

I am adding from non-array cells in an excel worksheet using simple =A1+B1+C1+D1.It works when A1 to D1 are all numbers but when one of the cell eg B1 is denoted as a text "N" (to represent None Attendance for the D1 event), I get an error as attached snapshot.

If possible, how would one use the SUM or SUMPRODUCTS formula versions to accept the N events without triggering an error in in the answer [this case?

N | N | N | 20:00:00 | 09:30:00 | 10 | 20:00:00 | 09:30:00 | 10 | 20:00:00 | 09:30:00 | 10 | 20:00:00 | 09:30:00 | 10 | 20:00:00 | 09:30:00 | 10 | 20:00:00 | 09:30:00 | 10 | #VALUE! | 10 |