Hi,
I need to do something like this:
COUNTIF(H2:H<current_row_number_-1>, "YES")
meaning that I need to reference a variable limit range inside the COUNTIF.
I tried:
COUNTIF(H2:COLUMN() & ROW() - 1, "YES")
and similars, but of course it does not work!
Just to be more clear: the result should COUNTIF(H2:H19, "YES") if I’m using the formula on the cell H20.
How can I do that correctly?
Thanks
Hello @jusinuri21 and welcome!
The most common functions that are recommended for your case are INDIRECT() and OFFSET() - with these you can get a range of cells that you pass to the COUNTIF() function.
Another approach - the one pointed out by the respected colleague @Villeroy - is the correct indication of the range address using a mixed address. Look carefully at the formulas proposed in the example. The range address for calculation begins with an absolute reference, marked with dollar signs for both column and row, and the second part, the end of the range, contains only one dollar sign, next to the row. This leads to the fact that when copying (stretching a cell with a formula), the end of the range will change along with the position of the formula.
So, you can use
=COUNTIF(INDIRECT("H2:H"&(ROW()-1));"Yes")
or
=COUNTIF(OFFSET($H$2;0;0;ROW()-2;1);"Yes")
or just
=COUNTIF($H$2:$H2;"Yes")
this was the solution! Thank you!
Which is the least flexible solution of those three, because as "H2:H" is a literal text string it doesn’t get adjusted when columns or rows are inserted or deleted before H2 (unless you want to always have it point to that range even if the cells were moved elsewhere).