Hi all, I hope someone can spot the problem here.
I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}
where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)
and the array formula is entered in Groups!E1 and returns an #N/A error.
If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.
If it calculates OK using F9, why can't excel resolve it in the cell??
When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.
The Groups array in column C is an array formula entered over 388 rows and contains:
{=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))}
where XCorel is another sheet in the same workbook.
Can anyone suggest why this might be?
If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.
I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}
where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)
and the array formula is entered in Groups!E1 and returns an #N/A error.
If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.
If it calculates OK using F9, why can't excel resolve it in the cell??
When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.
The Groups array in column C is an array formula entered over 388 rows and contains:
{=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))}
where XCorel is another sheet in the same workbook.
Can anyone suggest why this might be?
If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.
Last edited: