If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Array formula in merged cells
I have a simple array formula that sums 3 criterias. After I've entered
the formula and do Ctrl+Shift+Enter, an error pops up stating that "Array Formulas are not valid in merged cells.". I am a novice to array formulas so this is strange to me. Any way around this? Thanks, VT 
Ads 
#2




Array formula in merged cells
Hi
easiest way: remove the merged cells. I would assume you have entered this formula in a merged cell? Original Message I have a simple array formula that sums 3 criterias. After I've entered the formula and do Ctrl+Shift+Enter, an error pops up stating that "Array Formulas are not valid in merged cells.". I am a novice to array formulas so this is strange to me. Any way around this? Thanks, VT . 
#3




Array formula in merged cells
Unfortunately, the report's layout needs to have merged cells. I was
looking for an easy way out without redesigning the entire report. I guess I will have to look for other formulas to do the same function. Thanks anyway. VT 
#4




Array formula in merged cells
Hi#you may simply post your existing formula. There should
be an easy solution :) Original Message Unfortunately, the report's layout needs to have merged cells. I was looking for an easy way out without redesigning the entire report. I guess I will have to look for other formulas to do the same function. Thanks anyway. VT . 
#5




Array formula in merged cells
Here's my formula:
=Sum(sumif(Data1,ProductCode,Data2)) Data1 is a named column containing product codes. ProductCode is a named array containing the products for which I wish to add the sales figures. Data2 is a named column containing sales figures. Simple enough...only if it works in a merged cell!!! Thanks. 
#6




Array formula in merged cells
Hi
some (crude) workarounds: 1. Add several SUMIfs each comparing only to one of the values in your comparison range 2. Unmerge the cells, enter the formula in the leftmost cell and merge the cells again Original Message Here's my formula: =Sum(sumif(Data1,ProductCode,Data2)) Data1 is a named column containing product codes. ProductCode is a named array containing the products for which I wish to add the sales figures. Data2 is a named column containing sales figures. Simple enough...only if it works in a merged cell!!! Thanks. . 
#7




Array formula in merged cells
Thanks Frank, the 2nd method worked. This is really funny :)

#8




Array formula in merged cells
"Frank Kabel" wrote...
some (crude) workarounds: 1. Add several SUMIfs each comparing only to one of the values in your comparison range 2. Unmerge the cells, enter the formula in the leftmost cell and merge the cells again .... One more workaround: enter the formula as defined name, then refer to that defined name in the merged cell's formula. 
#9




Quote:
Intuitively, I tried hitting merge and center AFTER entering the array formula and it works fine. Just unmerge the cells, enter your array formula, hit Ctrl+Shift+Enter, then redo the merge and center. It's not awesome to take extra steps but it seems to work fine. FWIW this is in Excel 2010. Last edited by Methodician : May 12th, 2015 at 06:09 PM. Reason: Adding Excel version 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Arrary formula for Average includes blank cells  jan  Worksheet Functions  4  May 14th, 2004 11:19 PM 
Excel formula  select cells based upon fill color and then sum  Worksheet Functions  0  May 13th, 2004 07:45 PM  
Excel formula  select cells based upon fill color and then sum  Peo Sjoblom  Worksheet Functions  1  May 13th, 2004 06:52 PM 
Can't Sort Worksheet  Merged Cells Must be Identically Sized  Setting up and Configuration  1  February 4th, 2004 03:18 AM 