#ref error with Sumproduct

[dennis.davis]

Hello,
I'm getting a #ref error with the following formula. I haven't had a
problem with this until today.

=SUMPRODUCT(('ALL FF TLING'!$D$2:$D$1200=A$9)*('ALL FF
TLING'!$C$2:$C$1200=A10))

The range $D$2:$D$1200 is good and hasn't changed, as well as the
$C$2:$C$1200.

the reference to A$9 is how I want the formula to calculate for ease of
copying the formula to other cells, as well as the A10.

Like I said earlier, I had no problem with this formula in the previous
months, but when I made the monthly updates today, the error has popped
up. In the process of the updating, I do delete everything that is in
the 'ALL FF TLING' tab where the values in columns D and C are looked
at, but I have pasted data back into those cells.

Can anyone tell me why I'm getting this error?

Thanks.

 

[Carim]

Hi Dennis,

To be on the safe side, and avoid formatting issues :

=SUMPRODUCT(--('ALL FF TLING'!$D$2:$D$1200=A$9)*--('ALL FF
TLING'!$C$2:$C$1200=A10))


HTH
Carim

 

[dennis.davis]

Carim,

I put in the -- and it had no effect on the formula.

Or was the -- only to help with people looking at the posting trying to
provide input?

Thanks,
Dennis

 

[Guest]

Sorry Carim but using *-- won't do anything, that's just mixing syntaxes for
no good reason.

Dennis - most likely cause is probably #REF! errors somewhere in your
data...search and eliminate

 

[Dave Peterson]

Do you have any #ref! errors in D21200 or C2:C1200 of that "all ff tling"
worksheet?

 

[dennis.davis]

Dave and daddylonglegs,

I had searched prior to posting, but I slowly went through it all
again, and sure enough I had one #ref error in column C around cell
890.

I fixed that error and everything started working like a charm again.

Thanks for the help everyone!!

Dennis