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: