Array formula in merged cells

  • Thread starter Thread starter vtisix
  • Start date Start date
V

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
 
Hi
easiest way: remove the merged cells. I would assume you
have entered this formula in a merged cell?
 
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
 
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.
 
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
 
Frank Kabel said:
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
Click to expand...
....

One more work-around: enter the formula as defined name, then refer to that
defined name in the merged cell's formula.
 
Back
Top