error with formula
 

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

error with formula
 

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

error with formula
 

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.

"Max" wrote:

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

error with formula
 

"holyman" wrote:
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.

You're welcome. Guess you probably corrected the data.
Try also the suggested alternative.
It'll work even if the data contained #N/As
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---