CODEWITHSUNDEEP
excelexcel-formula
kfp
kfp

Reputation: 11

Using a variable for a data reference in COUNTIFS

I am summarizing data from a table in another workbook using sth like

COUNTIFS(filename.xlsm!Sheet[DataReference];">=1/1/2021";filename.xlsm!Sheet[DataReference];"<=31/3/2021")

I have create these formulas for 16 quarters and am using the same formula for many data references. To avoid errors (and trying to save manually editing the data reference in every cell twice), I would like to have a field in my row that holds the value for "DataReference" and use that as a variable inside the reference. This way I can copy&paste the formula in many rows and only need to plug in the right data reference and avoid further errors/inconsistencies.

But neither by googling nor by trial and error have I found a way for Excel to accept a variable inside the brackets

Upvotes: 0

Views: 39

Answers (1)

kfp
kfp

Reputation: 11

I found it! I need to use INDIRECT and build the path:

COUNTIF(INDIRECT(<cellwithfilename>&"!"&<Cell with worksheet name>&"["&<cell with data range>&"]"; ....

Upvotes: 1

Related Questions