I have a dynamic list, and need to count the items in a given column identified by a MATCH operation.
How do I convert (for example) COUNTA(G:G) to COUNTA(the entire column #7 returned by the MATCH).
I don't want to switch to RC format, but rather deal with it via formula.
I am so sure there is a simple solution, but for the life of me, can't find a single example.
Stay safe out there!
Jerry
Hi Jerry,
You can use the offset function:
COUNTA(G:G) will be:
COUNTA(Offset(A:A,0,Match(.....)-1)
Note that using full columns is not great for calculation engine, will be slower.
Thanks Catalin! I incorrectly assumed OFFSET required a single cell starting point.
And I cut down the range to a reasonable value of rows that shouldn't be exceeded.
Happy Holidays!
You're welcome 🙂
Offset can start from any range size, you have also optional arguments to resize the output range width and height if needed.
