Forum Discussion
Issue with converting Google Sheet into Excel
My formulas in cells D3:d5, H3:I5, M3:N5, S3:T5, and X3:Z5 are not working. Hoping to understand how to adjust them to work in the attached spreadsheet. Any help would be greatly appreciated!
Example from H3:
=IFERROR(@__xludf.DUMMYFUNCTION("IFERROR(COUNTIF(FILTER({H$13:L$105}, {$E$13:$E$105}=$C3), ""<>n/a""), 0)"),10)
That means Google Sheets formula is not compatible with Excel. Google Sheets adds such combination with @__xludf.DUMMYFUNCTION and current static value returned by IFEEROR, in your sample 10, to avoid errors in file saved as Excel workbook.
If take one of such formulae, e.g. in D3
=IFERROR(@__xludf.DUMMYFUNCTION( "IFERROR(COUNTIF(FILTER({$C$13:$C$105}, {$E$13:$E$105}=$C3), ""<>""),0)" ),2)we may take second line
=IFERROR(COUNTIF(FILTER({$C$13:$C$105}, {$E$13:$E$105}=$C3), ""<>""),0)and try to transform it into correct Excel formula.
Here are two points - in Excel we don't need {} to transform range to array; and COUNTIF() doesn't work with array as first parameter, only with range.
Equivalent formula could be
=IFERROR( ROWS( FILTER($C$13:$C$105, ($E$13:$E$105=$C3)*($C$13:$C$105 <> "") ) ), 0 )It is in D1 in attached.
3 Replies
That means Google Sheets formula is not compatible with Excel. Google Sheets adds such combination with @__xludf.DUMMYFUNCTION and current static value returned by IFEEROR, in your sample 10, to avoid errors in file saved as Excel workbook.
If take one of such formulae, e.g. in D3
=IFERROR(@__xludf.DUMMYFUNCTION( "IFERROR(COUNTIF(FILTER({$C$13:$C$105}, {$E$13:$E$105}=$C3), ""<>""),0)" ),2)we may take second line
=IFERROR(COUNTIF(FILTER({$C$13:$C$105}, {$E$13:$E$105}=$C3), ""<>""),0)and try to transform it into correct Excel formula.
Here are two points - in Excel we don't need {} to transform range to array; and COUNTIF() doesn't work with array as first parameter, only with range.
Equivalent formula could be
=IFERROR( ROWS( FILTER($C$13:$C$105, ($E$13:$E$105=$C3)*($C$13:$C$105 <> "") ) ), 0 )It is in D1 in attached.
Thank you, SergeiBaklan! Excel not needing the {} for arrays is what threw me off. Much appreciated!
gsquared2 , glad to help
