0

Not sure why this wouldn't work:

COUNTIF(OFFSET(OtherSheet!F5,0,0,4,1),"<10")

Trying to count values less than 10 in 4 rows of another worksheet. Of course, I can use OtherSheet!F5:F8 instead of the OFFSET like so:

COUNTIF(OtherSheet!F5:F8,"<10")

and this works fine. but I'm trying to work up to where the "4" is dynamically determined. But, I can't even seem to get the OFFSET to work correctly when the range is in another spreadsheet.

Thanks! Derek

Derek Martin
  • 1

1 Answers1

1

Don't know about OFFSET, but it is volatile and I avoid it whenever possible. I would use INDEX, which is not volatile:

=COUNTIF(Sheet1!$F$5:INDEX(Sheet1!$F$5:$F1040000,4),"<10")

Replace the 4 with the cell in which you want to place the size of the array.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81