Attempted to make equation with maxIF dynamic however end up with errors (in excel)-CodePudding

I have an equation that references a dynamic dataset… When referencing these dynamic columns I initially just used the range row 3 to row 2000 (as this range is a lot longer than my actual dataset). Unfortunately when I refresh the data it changes this range and messes up the whole equation… So I attempted to make it dynamic.

The initial equation I wrote (ie referencing row 3 to row 2000) as follows.

=IF(MAXIFS($EA$3:$EA$2000,Book1!$C$3:$C$2000,Book1[@[project_id]])=Book2!EA3,MAXIFS($EA$3:$EA$2000,Book1!$C$3:$C$2000,Book1[@[project_id]]),0)

This is my attempt at making it dynamic (so I don’t need to keep on fiddling around with the equation):

=IF(MAXIFS($EA$3:INDEX($EA$3:$EA$2000,COUNTA($EA$3:$EA$2000)),Book1!$C$3:INDEX($C$3:$C$2000,COUNTA($C$3:$C$2000)),Book1[@[project_id]])=Book2!EA3,MAXIFS($EA$3:INDEX($EA$3:$EA$2000,COUNTA($EA$3:$EA$2000)),Book1!$C$3:INDEX($C$3:$C$2000,COUNTA($C$3:$C$2000)),Book1[@[project_id]]),0)

But I get a #VALUE error and unsure how to resolve this.

CodePudding user response：

The ranges passed to MAXIFS must be of an equal size, which means that COUNTA($C$3:$C$2000) and COUNTA($EA$3:$EA$2000) must be equal, which I suspect they are not (you can easily verify).

You are correct to attempt to restrict your ranges, as this in general is good practice: some functions, however, including MAXIFS, employ implicit detection of the last-used cells within the ranges passed, effectively meaning that you can get away with referencing entire columns with no detriment to calculation performance. As such, that would be my recommendation to you, i.e. use $EA:$EA in place of $EA$3:$EA$2000, etc.