How to enter array formulas in merged cells
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: Array formulas are not valid in merged cells.
This is not true, they are allowed in merged cells. Here are a few suggestions to solve this problem.
Unmerge cells
 Select the merged cell
 Click "Merge & Center" button to unmerge cells
 Select first cell
 Enter array formula
 Select cells
 Click "Merge & Center" button to merge cells
Tip! You donยดt need to repeat these actions for all cells below, just click and hold on black dot on lower right corner of cell and drag down as far as needed.
Excel defined named ranges
 Go to tab "Formula" on the ribbon
 Click "Name Manager"
 Type a name, I named it arrayformula

Enter the array formula in "Refers to:" field
 Click OK button
 Select the merged cell
 Type =arrayformula in formula bar
 Press enter
Recommended posts
 Absolute and relative references
 Insert an array formula using vba
 Extract a unique distinct list using array formulas
If you want to learn more about array formulas join Advanced excel course.
This post will teach you how to work with excel arrays in visual basic for applications (VBA). Why do you [โฆ]
Array formulas allows you to do advanced calculations not possible with regular formulas.
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me [โฆ]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones [โฆ]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, [โฆ]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and [โฆ]
2 Responses to โHow to enter array formulas in merged cellsโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Hi Oscar,
Greetings. I was trying using the ARRAY Formula in the merged Cell, but facing an issue.
For a simple formula in the merged cell, the formula as explained on the website works perfectly but when I try to use it in another merged cell which contains HYPERLINK, I get #REF. I have pasted the code here for your reference. if you could please let me know what is wrong in this code.
=HYPERLINK(INDEX('01082011'!$C$4:$L$129,MATCH(SEARCH!$C$2,'01082011'!$C$4:$C$1159,0),11))
Thanks and regards,
Ali.
Done... Its Resolved :D
Thanks and regards,
Ali.