SUMPRODUCT Formula doesn't work past some rows

[NikToo]

I'm having real issues with a SUMPRODUCT formula. It works with 2000 rows, but not 3000. It's really weird.

=SUMPRODUCT(('Lead Sales Data'!$E$4:$E$3000=B29)*('Lead Sales Data'!$Q$1:$NO$1=L$4)*'Lead Sales Data'!$Q$4:$NO$3000)

Criteria 1 is in B29, the second criteria L4 has week numbers which repeat in the data as the data is split by day. So it needs to add up several columns with the same header.

And it works for 2000 rows, but conks out at 3000. Why would that be?

 

[RoryA]

What exactly does "conks out" mean?

Also, are there multiple rows that match the criteria in column B, or just one?

 

[NikToo]

Gives a #N/A in the cell.

And yes, there are many occurrences of the criteria in B29.

 

[RoryA]

Are you sure there aren't any #N/A errors in the data in rows 2000-3000? Your formula works in 2016.

 

[NikToo]

There was a #N/A hiding in there. Which is weird because there shouldn't be, but that's another headache. Thanks. Weird that it makes it break. But thanks again.