0

I recently ran into the following issue:

 =sumproduct($H$6:$H,Regexmatch($C$6:$C,"Order.*")*($I$6:$I=Q$1))

Worked, while

 =sumproduct($L$6:$L,Regexmatch($C$6:$C,"Order.*")*($I$6:$I=Q$1))

Didn't. Google's error message wasn't terribly helpful. The only difference is the H versus L range.

3

Since the only difference was one of the ranges there had to be something wrong in that range. Since the range is 3000 rows long, I didn't try to scan for it.

I proceed to do a divide and conquer search, first verifying if I changed the end point for each range to 20.

=sumproduct($L$6:$L20,Regexmatch($C$6:$C20,"Order.*")*($I$6:$I20=Q$1))    

Since that worked and the whole column didn't, I proceeded to double the 20 to 40, then 80.

At 80 it broke again. Step half way back. At 60 ok. Step halfway forward At 70 it didn't. At 65 it didn't. Looked there and I had an #N/A from another lookup.

The error message that SumProduct showed was the same as the #N/A box showed. So perhaps in a sense the error message made sense.

But I wish googlesheets would give a better message like, "Bad Data L64"

I have turned in feedback to this effect.

  • 3
    I spent an hour and a half searching for the answer. After I finally fixed it, I figured I put the solution to it up here. Several times before I have had a problem, asked on SE, solved it myself, put it on an SE site, then found my own answer a couple years later. – Sherwood Botsford Jan 29 '18 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.