Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result.
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray

Try entering it as a regular (non-array) formula and copying it down. Works
for me.

Hope this helps,

Hutch

"Murray" wrote:

Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result.
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray

On Feb 5, 10:05*am, Tom Hutchins
wrote:
Try entering it as a regular (non-array) formula and copying it down. Works
for me.

Hope this helps,

Hutch



"Murray" wrote:
Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result.
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray- Hide quoted text -

- Show quoted text -

Thanks Tom, but that only seems to work if the row number of the value
in column A matches its counterpart in rows 2-1900.

I seem to have found a solution by changing it to
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900),
0))
but I'm still unsure as to why this works and the other one does not.

Murray

=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0))
The numbers in column I are formatted as text
hence the VALUE function.

Try this normally entered formula:

=SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900)

--
Biff
Microsoft Excel MVP


"Murray" wrote in message
...
On Feb 5, 10:05 am, Tom Hutchins
wrote:
Try entering it as a regular (non-array) formula and copying it down.
Works
for me.

Hope this helps,

Hutch



"Murray" wrote:
Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result.
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray- Hide quoted text -

- Show quoted text -

Thanks Tom, but that only seems to work if the row number of the value
in column A matches its counterpart in rows 2-1900.

I seem to have found a solution by changing it to
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900),
0))
but I'm still unsure as to why this works and the other one does not.

Murray

Thanks Biff

With a few modifications to deal with errors induced by blank values,
and array entering it, it worked really well.

Murray


On Feb 5, 12:27*pm, "T. Valko" wrote:
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0))
The numbers in column I are formatted as text
hence the VALUE function.

Try this normally entered formula:

=SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900)

--
Biff
Microsoft Excel MVP

"Murray" wrote in message

...
On Feb 5, 10:05 am, Tom Hutchins





wrote:
Try entering it as a regular (non-array) formula and copying it down.
Works
for me.

Hope this helps,

Hutch

"Murray" wrote:
Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result..
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray- Hide quoted text -

- Show quoted text -

Thanks Tom, but that only seems to work if the row number of the value
in column A matches its counterpart in rows 2-1900.

I seem to have found a solution by changing it to
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900),
0))
but I'm still unsure as to why this works and the other one does not.

Murray- Hide quoted text -

- Show quoted text -

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Murray" wrote in message
...
Thanks Biff

With a few modifications to deal with errors induced by blank values,
and array entering it, it worked really well.

Murray


On Feb 5, 12:27 pm, "T. Valko" wrote:
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0))
The numbers in column I are formatted as text
hence the VALUE function.

Try this normally entered formula:

=SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900)

--
Biff
Microsoft Excel MVP

"Murray" wrote in message

...
On Feb 5, 10:05 am, Tom Hutchins





wrote:
Try entering it as a regular (non-array) formula and copying it down.
Works
for me.

Hope this helps,

Hutch

"Murray" wrote:
Greetings

I have an array formula that I have entered (using CTRL+SHIFT+ENTER)
as follows:

=IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA())

This works fine on the first row I entered it into and gives a result.
However, when I copy it down so it references A3, A4 etc, it always
returns #N/A for every subsequent row.

Possibly useful other information:
1. The numbers in column I are formatted as text, hence the VALUE
function.
2. Thinking the CONCATENATE might be the problem I created another
column with the concatenated result and referenced that instead, but
to no avail.

Any ideas why it doesn't work?

Thanks

Murray- Hide quoted text -

- Show quoted text -

Thanks Tom, but that only seems to work if the row number of the value
in column A matches its counterpart in rows 2-1900.

I seem to have found a solution by changing it to
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900),
0))
but I'm still unsure as to why this works and the other one does not.

Murray- Hide quoted text -

- Show quoted text -