Max, absolutely brilliant and so simple too.

Thankyou very much!!

Rob

"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rob" wrote in message
...
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