reference a cell value inside a formula (indirect)

1
votes

please I need help with indirect (reference a cell value) and use that value in a formula

hi, as you know, array formulas get crazy with thousands of rows for no reason, so I want to limit my arrays using a referenced cell value using indirect, but I always fail to write it right for some reason.

=COUNTA(L2:L)

this gives me the value I need = reference value

now I want to reference that cell value inside a formula like

=UNIQUE(M2:M reference)

=ArrayFormula(COUNTIF(M2:M reference;UNIQUE(P2:P reference)))

I know I have to use indirect, but the syntax always fails for me

1
if-statementgoogle-sheetsreferencegoogle-sheets-formulaarray-formulas

1 Answers

0
votes
=ARRAYFORMULA(IF(LEN(M2:M&P2:P), COUNTIF(M2:M, P2:P), ))

0

=ARRAYFORMULA(COUNTIF(INDIRECT("M2:M"&COUNTA(M2:M)+1), 
                      INDIRECT("P2:P"&COUNTA(P2:P)+1)))

0