Tue 13 Jun 2017

Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

Sat 05 Jul 2014

Tips & Tricks 27 - Find the Merged Cells

By |Saturday, July 05th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Sometimes, you would be required to find merged cells in Excel. One of the common cases is when you try to sort a column and all of a sudden of you see the pop-up regarding merged cell. You can do following to find merged cells -

1. Select the column.
2. CTRL+F and click Options.
3. Click on Format
4. Alignment tab > Remove ticks from Wrap Text and Shrink to Fit. Tick Merge Cells.
5. Now click Find Next to find if Column has merged cell or not.

1