Before watching the video, try solving it yourself. Here is the deal:
You need to find a formula that solves the below problem:
The tricky part is this:
If B, D or R win a price, the workshop (i.e. that row) counts as 1. This means if we only had data until row 10, we need to get 4 as our answer! (…the data set is bigger than this…)
How would you go about it? Use intermediate steps? Use one formula?
You can download the file below:
Don’t watch the video until you attempt it yourself…
…then you can see how I tried to solve it with 1 Array Formula.
p.s. Honestly I shot this video over 10 times! It was originally intended to be 1 video, showing 1 solution, but every time I started editing the video, I came up with new formulas that could do this, so I kept shooting it over and over again with different solutions and finally it became a three series video. Hope you enjoy it and learn more about Excel array formulas!
Move on to PART 2 to learn about the MMULT formula
Move on to PART 3 to learn about the MMULT formula
Download the Workbook Below…
Download File
You’d like to try it yourself? Download the FREE file here. Let me know what you come up with!
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.
