I have a table
I want to return the number of all the cells in a column which is equal to "Defined"
I don't know to how to refer to the last line of my column in the following formula
=SUMPRODUCT(SUBTOTAL(3;OFFSET(B2:B?;ROW(B2:B?)-ROW(B2);;1)); --(B2:B?="Defined"))
please check the question marks in my formula.
If you are using a table, you can refer to the column and it will take the entire range. Just click on the header of the table column and it will add something like "Table1[Column1]" to refer to the whole column.
Otherwise, pick some arbitrarily large number in your rows, like 100000.
EDIT: After some testing, it doesn't appear the formula would like to use the Table reference, but picking some large number as your furthest down row works.
Also, I had to update the formula to use commas instead of semicolons and tweaked the criteria, here is the final one that worked for me: =SUMPRODUCT( ( (B2:B5000="Defined"))*(SUBTOTAL(3,OFFSET(B2,ROW(B2:B5000)-MIN(ROW(B2:B5000)),0))))
If you have Excel for Microsoft 365, you could try this
XLOOKUP(TRUE, B:B <> "", B:B, , , -1)MAP(b_idx, LAMBDA(i, SUBTOTAL(103, INDEX(b_, i, 1))))For example, with B11, B12 = Defined and rows 11, and 12 hidden, enter the following whether you want the count:
=LET(
last_b, XLOOKUP(TRUE, B:B <> "", B:B, , , -1),
b_, B2:last_b,
b_idx, SEQUENCE(ROWS(b_)),
b_visible, MAP(b_idx, LAMBDA(i, SUBTOTAL(103, INDEX(b_, i, 1)))),
b_defined, EXACT(b_, "Defined"),
SUM(b_visible * b_defined)
)
=LET(
b_, Table1[Col b],
b_idx, SEQUENCE(ROWS(b_)),
b_visible, MAP(b_idx, LAMBDA(i, SUBTOTAL(103, INDEX(b_, i, 1)))),
b_defined, EXACT(b_, "Defined"),
SUM(b_visible * b_defined)
)
