Formula query

[Guest]

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 Phillips]

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)

 

[Guest]

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