# Is there a limit on referencing cells?

1. ## Is there a limit on referencing cells?

For example in this formula I typed in WWW as the max:

=SUMPRODUCT((\$F\$5:\$WWW\$5>=C2)*1,(\$F\$5:\$WWW\$5<=C3)*1,ABS(\$F\$6:\$WWW\$6))

If I try to type in a value higher than WWW it will give me an error.

I was just trying to put in an amount that I would never hit within the row.

2. ## Re: Is there a limit on referencing cells?

try with =SUMPRODUCT(5:5>=C2...

3. ## Re: Is there a limit on referencing cells?

I dont' think that will work as it needs to start at column F, I have data in the first few columns.

4. ## Re: Is there a limit on referencing cells?

You did not describe your sheet so this may not work, but:
Given an unused cell, say, K3, you could use the INDIRECT fn to build the address range:
Then, your SUMPRODUCT fn would be:

5. ## Re: Is there a limit on referencing cells?

Thanks for the help.

6. ## Re: Is there a limit on referencing cells?

I suspect the issue is down to the contents of the cells beyond WWW than anything else... the range itself should have no bearing.

Also generally speaking the double unary method of coercion is regarded as being quicker than *1 etc...

=SUMPRODUCT(--(\$F\$5:\$WWW\$5>=C2),--(\$F\$5:\$WWW\$5<=C3),ABS(\$F\$6:\$WWW\$6))

If you have text values in your final range you will get an error, if you have error values in any one of the ranges you will get an error... if the ranges used are not of equal size when you re-reference beyond WWW you will get an error.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1