Formula fails if cells text format
 

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

Formula fails if cells text format
 

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

Formula fails if cells text format
 

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

Formula fails if cells text format
 

Welcome, Rob.
Thanks for the feedback !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rob" wrote in message
...
Max, absolutely brilliant and so simple too.

Thank you very much!!

Rob