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’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
