Array formula in merged cells

[vtisix]

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

 

[Frank Kabel]

Hi
easiest way: remove the merged cells. I would assume you
have entered this formula in a merged cell?

 

[vtisix]

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

 

[Frank Kabel]

Hi#you may simply post your existing formula. There should
be an easy solution

 

[vtisix]

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.

 

[Frank Kabel]

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

 

[vtisix]

Thanks Frank, the 2nd method worked. This is really funny

 

[Harlan Grove]

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.