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
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
