CODEWITHSUNDEEP
excelformula
Doug
Doug

Reputation: 207

Adding excel formula as part of a range

I am looking to add a formula as part of a range reference. Below is an example:

=sumif(F2:indirect("F" & B5),....and so on....)

Essentially I have a lot of formulas referencing a range of data that is always changing in terms of the number of rows. The goal is as the row number changes, users can just enter in the new row number and all the formulas will update automatically. Cell B5 houses the row number and is the input cell that users would update.

I have tried many different methods, everything from INDIRECT, CELL, to other methods and cannot get it to work. I have formatted B5 as text, number, and general but it still does not work.

Any ideas on how to make this work? I have seen similar questions asked on other forums and tried their methods and still get nothing but #VALUE.

Upvotes: 1

Views: 243

Answers (1)

barry houdini
barry houdini

Reputation: 46371

INDEX is better than INDIRECT - you can use this as a range in SUMIF

F2:INDEX(F:F,B5)

where B5 contains a number

although SUMIF/COUNTIF etc. usually calculate using only the "used range" so it's often as efficient just to use the whole column like F:F, even if you only have 500 rows of data

Upvotes: 6

Related Questions