error with formula

H

holyman

I have the following formula which worked on original data

=SUMPRODUCT(--('Raw data'!$AF$1:$AF$5291="0001-01-01"),--('Raw
data'!$L$1:$L$5291="railcomm1"))

In using this for new data, the result comes back with an #N/A, yet all data
is in the same place, but in a different order.

It won't let me trace the error, and can't see why its wrong. Please help

Column L Column AF
RAILCOMM0 12/07/2006
RAILCOMM1 0001-01-01
RAILCOMM1 0001-01-01
BTCAR1 0001-01-01
BTCOMM1 0001-01-01
BTCOMM0 12/07/2006
BTCOMM1 0001-01-01
BTCOMM1 0001-01-01
BTCOMM0 12/07/2006
BTCOMM1 0001-01-01
BTCOMM1 0001-01-01
GOVERNMENT DEPARTMENTSCAR1 0001-01-01
GOVERNMENT DEPARTMENTSCAR1 0001-01-01
GOVERNMENT DEPARTMENTSCAR1 0001-01-01
 
M

Max

.. In using this for new data, the result comes back with an #N/A

Probably there's an #N/A somewhere within the new data in either cols L or
AF which is throwing things off

Try this instead:
=SUMPRODUCT((ISNUMBER(SEARCH("railcomm1",'Raw
data'!$L$1:$L$5291))*(ISNUMBER(SEARCH("0001-01-01",'Raw
data'!$AF$1:$AF$5291)))))
 
H

holyman

Such a simple reason why it wouldn't work........totally right, had #N/A's in
the data. Thanks muchly, should of seen it myself mind.
 
M

Max

holyman said:
Such a simple reason why it wouldn't work........totally right, had #N/A's in
the data. Thanks muchly, should of seen it myself mind.
Click to expand...

You're welcome. Guess you probably corrected the data.
Try also the suggested alternative.
It'll work even if the data contained #N/As
 
Top