Issue with converting Google Sheet into Excel

New Contributor

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)




3 Replies
best response confirmed by gsquared2 (New Contributor)


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(COUNTIF(FILTER({$C$13:$C$105}, {$E$13:$E$105}=$C3), ""<>""),0)"

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, @Sergei Baklan! Excel not needing the {} for arrays is what threw me off. Much appreciated!

@gsquared2 , glad to help