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 |
#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
|
|||
|
|||
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 re-do 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 02:18 AM |