Log In
Register
Home
October 9, 2014
Offline
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
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Answers Post
October 9, 2014
Offline
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!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
