I have two very similar workbooks. I created the following formula in the
first workbook :
=SUMPRODUCT((('issue detail'!$A$2:$D$844)=$A4)*('issue
detail'!$C$2:$C$844))*-1
Cell A4 is blank and results in 0 until I copy the formula to cell A6 which
contains a specific reference. This formula does exactly what I want it to
do.

I copied the formula to the second workbook and made the changes to the
ranges shown:
=SUMPRODUCT((('issue detail'!$A$2:$J$150)=$A3)*('issue detail'!$J$2:$J$150))
Cell A3 is also blank but, now the formula results in a number and I cannot
see what I have done to create this result. When I copy the formula to
another cell (A5) the formula calculates the data correctly.

As an FYI, I am using the first workbook to calculate usage data by week for
about 2500 items. I leave the weeks (columns) without data blank until data
is available and then copy the formula. Also, once the data is calculated, I
change the calculated data to Value to eliminate constant recalulation. I am
doing the same in the second WB for different data.

Thanks Ron R.

Perhaps there are blanks in A2:J150, so maybe you need

=SUMPRODUCT(('issue detail'!$A$2:$J$150<"")*('issue
detail'!$A$2:$J$150=$A3)*('issue detail'!$J$2:$J$150))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ronnomad" wrote in message
...
I have two very similar workbooks. I created the following formula in the
first workbook :
=SUMPRODUCT((('issue detail'!$A$2:$D$844)=$A4)*('issue
detail'!$C$2:$C$844))*-1
Cell A4 is blank and results in 0 until I copy the formula to cell A6
which
contains a specific reference. This formula does exactly what I want it
to
do.

I copied the formula to the second workbook and made the changes to the
ranges shown:
=SUMPRODUCT((('issue detail'!$A$2:$J$150)=$A3)*('issue
detail'!$J$2:$J$150))
Cell A3 is also blank but, now the formula results in a number and I
cannot
see what I have done to create this result. When I copy the formula to
another cell (A5) the formula calculates the data correctly.

As an FYI, I am using the first workbook to calculate usage data by week
for
about 2500 items. I leave the weeks (columns) without data blank until
data
is available and then copy the formula. Also, once the data is
calculated, I
change the calculated data to Value to eliminate constant recalulation. I
am
doing the same in the second WB for different data.

Thanks Ron R.

Bob,

I just check again and no blanks. Again, as far as I can see, with the
exception of the 'ranges' (and the fact that I multiply the first by minus 1
- because all the raw data is in negative numbers and I want a positive
result) the two formulae are the same.

Ron R

"Bob Phillips" wrote:

Perhaps there are blanks in A2:J150, so maybe you need

=SUMPRODUCT(('issue detail'!$A$2:$J$150<"")*('issue
detail'!$A$2:$J$150=$A3)*('issue detail'!$J$2:$J$150))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ronnomad" wrote in message
...
I have two very similar workbooks. I created the following formula in the
first workbook :
=SUMPRODUCT((('issue detail'!$A$2:$D$844)=$A4)*('issue
detail'!$C$2:$C$844))*-1
Cell A4 is blank and results in 0 until I copy the formula to cell A6
which
contains a specific reference. This formula does exactly what I want it
to
do.

I copied the formula to the second workbook and made the changes to the
ranges shown:
=SUMPRODUCT((('issue detail'!$A$2:$J$150)=$A3)*('issue
detail'!$J$2:$J$150))
Cell A3 is also blank but, now the formula results in a number and I
cannot
see what I have done to create this result. When I copy the formula to
another cell (A5) the formula calculates the data correctly.

As an FYI, I am using the first workbook to calculate usage data by week
for
about 2500 items. I leave the weeks (columns) without data blank until
data
is available and then copy the formula. Also, once the data is
calculated, I
change the calculated data to Value to eliminate constant recalulation. I
am
doing the same in the second WB for different data.

Thanks Ron R.