The default setting is Sum, which adds the corresponding cell values of the Source data range and gives the result in the target range. This specifies how the values of the consolidation ranges will be calculated. Select a function from the Function list.

Copy results to takes only the first cell of the target range instead of the entire range as is the case for Source data range. If the target range is not named, click in the field next to Copy results to and enter the reference of the target range or select the range using the mouse or position the cursor in the top left cell of the target range.

Specify where you want to display the result by selecting a target range from the Copy results to drop-down list.

Select additional ranges and click Add after each selection.

The selected range is added to the Consolidation ranges list. (You may need to move the Consolidate dialog or click on the Shrink icon to reach the required cells.)

If the source range is not named, click in the field to the right of the drop-down list and either type a reference for the first source data range or use the mouse to select the range on the sheet. The Source data range list contains any existing named ranges (created using Data > Define Range), so you can quickly select one to consolidate with other areas.

How to merge cells in openoffice excel