CODEWITHSUNDEEP
excelif-statementgoogle-sheetsexcel-formulagoogle-sheets-formula
jrob11
jrob11

Reputation: 315

Range based on a certain number of columns/rows

I'm not entirely sure how to word this correctly, but here goes!

I have a formula, let's say: =IF(SUM(A1:A10)=50,"Yes","No")

Is there a way that I can identify the range based on the number of rows I want to include based on the value of another cell?

Let's say that cell B1 value is "10", is there a way to re-write my formula to look something like this:

=IF(SUM(A1:X)=50,"Yes","No") where "X" is some sort of formula that calculates "A1 + 10 Rows" (10 would be the variable number based on value of B1, so "A1 + B1 rows")

I need to use the same technique to also adjust the number of columns for another formula as well.

Basically, I'm using a VLookUp with multiple arrays, and the arrays have to be the same size. The amount of columns or rows is dynamic.

So I need to be able to take write a formula, =COUNTIF($F$1:$F$10<>"")-1 maybe that formula calculates "4".

So I need to adjust my VLookUp to A1 + 4 rows as the searchable range, and for another formula, it would be A1 + 4 columns (so A1:E1).

I hope that makes sense.

Upvotes: 0

Views: 483

Answers (2)

player0
player0

Reputation: 1

in Google Sheets:

=IF(SUM(INDIRECT("A1:A"&B1))=50, "Yes", "No")

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Put your value in B1, then in another cell:

=IF(SUM(INDEX(A:A,1):INDEX(A:A,B1))=50,"Yes","No")

enter image description here

This example uses the value in B1 to define the number of rows to be SUM()'ed. It avoids having to use INDIRECT().

Upvotes: 1

Related Questions