# referring to a cell

This is a discussion on referring to a cell within the Excel Questions forums, part of the Question Forums category; This ties into my previous question. The answer I was looking for was: =SUMPRODUCT(--(E4:E28+F4:F28 my next question is how can ...

1. ## referring to a cell

This ties into my previous question. The answer I was looking for was:

=SUMPRODUCT(--(E4:E28+F4:F28<5))

my next question is how can I replace the "28" in E28 and F28 with the number contained in cell B2?

B2 specifies how big the range is and I want the =SUMPRODUCT(--(E4:E28+F4:F28<5)) to only go from E4 to however big the range is based on value in B2.

for example;

B2=30

so =SUMPRODUCT(--(E4:E30+F4:F30<5))

B2=14

=SUMPRODUCT(--(E4:E14+F4:F14<5))

I hope this can be done...

thanks

jmelon

2. ## Re: referring to a cell

=SUMPRODUCT(--(OFFSET(E4,0,0,B2-3)+OFFSET(F4,0,0,B2-3)<5))

Does this work for you?

3. ## Re: referring to a cell

did not work. the original count was 10 and with the offset it came in at 8

4. ## Re: referring to a cell

=SUMPRODUCT(--(OFFSET(E4,0,0,B2-1)+OFFSET(F4,0,0,B2-1)<5))

5. ## Re: referring to a cell

=SUMPRODUCT(--(OFFSET(E4,0,0,B2-1)+OFFSET(F4,0,0,B2-1)<5))

it just needs to refer to B2 by itself without subtracting anything.

=SUMPRODUCT(--(OFFSET(E4,0,0,B2)+OFFSET(F4,0,0,B2)<5))

thanks

jmelon

6. ## Re: referring to a cell

=SUMPRODUCT(--(OFFSET(E4,0,0,B2-1)+OFFSET(F4,0,0,B2-1)<5))

it just needs to refer to B2 by itself without subtracting anything.

=SUMPRODUCT(--(OFFSET(E4,0,0,B2)+OFFSET(F4,0,0,B2)<5))

thanks

jmelon

#### Posting Permissions

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