Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula fails to update | Excel Discussion (Misc queries) | |||
moving column/cell data to rows/cells fails | Excel Worksheet Functions | |||
Formula fails in text formatted cell | Excel Worksheet Functions | |||
format cells when containing specified text | Excel Discussion (Misc queries) | |||
VBA Formula Fails to Execute | Setting up and Configuration of Excel |