Formulas don't count merged cells

Unfortunately, your colleague is making a basic error in spreadsheet design ... mixing raw data with analysis and presentation. Excel works best with simple tables of data. Given that, you can sort, filter, produce pivot tables and charts ... the world is your lobster.

But hey, it's his spreadsheet, you've told him why it's a problem, we've supported you and offered some solutions ... it's down to him how he moves forward, or continues to struggle. If it were me, I'd back away quietly.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save