Formula fails if cells text format

[Rob]

I frequently use a formula such as the following which enables me to give a
result from a column of data if I have 2 or more criteria.
=SUMPRODUCT(--($C$2:$C$159=A5),--($D$2:$D$159=B5),$E$2:$E$159)

I find that if the data in column E is a "number stored as text" (as advised
by the exclamation mark in cells with a green triangle) the result of the
formula is given as 0. I know I can convert these cells to a number, which
will fix the problem, but is there a way to not have to bother with this so
that the formula will still pick up the data that matches the criteria?
Maybe a completely different formula is needed?? It usually occurs if the
data is imported into Excel and I'd like to not have to reformat the data,
but still have a formula that will pick up the information.

In the above formula A5 is a surname, B5 is a Christian name and the column
range E2:E159 holds ages. For some reason the ages are imported to Excel
not as numbers.

Rob

 

[Max]

Just coerce col E's range with a "+0", viz.:
=SUMPRODUCT(--($C$2:$C$159=A5),--($D$2:$D$159=B5),$E$2:$E$159+0)

 

[Rob]

Max, absolutely brilliant and so simple too.

Thankyou very much!!

Rob

 

[Max]

Welcome, Rob.
Thanks for the feedback !