There are two tricky parts here: one is that my check list includes multiple items and two, the unique count is across rows instead of columns.
In Part 2, I used MMULT (Matrix Multiplication) to count unique items across rows.
This became my favorite solution. I then added the TRANSPOSE formula to it to be able to expand my columns automatically.
Problem with TRANSPOSE is that it requires Control Shift Enter (CSE). I’d rather have a formula that doesn’t require CSE. After some brainstorming, I decided to use the ROW function. The challenge was to get my rows to depend on my columns. I ended up with three different formulas that replaced the TRANSPOSE function. These were the function combinations I used:
- INDIRECT & ROW
- INDIRECT & ROW & ADDRESS
- ROW & INDEX
The original purpose of this video was to show you how the INDIRECT function can be used here. Which I did, but my favorite formula ultimately became the one that doesn’t include INDIRECT. It’s the one with ROW and INDEX – where INDEX is used to return a cell reference instead of a cell value (Isn’t the INDEX function amazing?!)
Sounds complicated? Some things are not meant to be described. The video will help.
p.s. Do share any alternate versions you can think of to solve this.
Download the FINAL Workbook Below…
Download File
You’d like to try it yourself? Download the FREE file here. Try your own versions too!
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
