Option 1: If it’s a one time only solution then watch this method created by Chandoo: YouTube Video
But what if you have to repeat this solution daily or someone else asks for a live solution (drop in the data and the solution is instant) ?
Option 2: Use a quick VBA (macro) sub procedure to quickly zip through your data. YouTube Video
Option 3: If you’re not allowed to use macros or vba code is a foreign language to you then watch my video below that uses formulas.
Watch my YouTube video
Download my Excel file
What are the ingredients to my solution?
I combined these functions to create my solution: IF, ISNUMBER, AND, NOT, SUMIF
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.