A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Array formula in merged cells



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2004, 03:43 PM
vtisix
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 04:42 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 04:56 PM
vtisix
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 05:04 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 05:13 PM
vtisix
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 05:23 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old July 8th, 2004, 05:28 PM
vtisix
external usenet poster
 
Posts: n/a
Default Array formula in merged cells

Thanks Frank, the 2nd method worked. This is really funny :-)
  #8  
Old July 8th, 2004, 05:54 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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 work-around: enter the formula as defined name, then refer to that
defined name in the merged cell's formula.


  #9  
Old May 12th, 2015, 06:08 PM
Methodician Methodician is offline
Member
 
First recorded activity by OfficeFrustration: May 2015
Posts: 1
Default

Quote:
Originally Posted by vtisix View Post
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
While I realize this question was posted years ago, for anyone else I FOUND THE ANSWER.

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.