 SOLVED

# 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)``

3 Replies
best response confirmed by gsquared2 (New Contributor)
Solution

# Re: Issue with converting Google Sheet into Excel

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.

# Re: Issue with converting Google Sheet into Excel

Thank you, @Sergei Baklan! Excel not needing the {} for arrays is what threw me off. Much appreciated!