SOLVED

Issue with converting Google Sheet into Excel

Copper 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 (Copper Contributor)
Solution

@gsquared2 

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

@gsquared2 , glad to help

1 best response

Accepted Solutions
best response confirmed by gsquared2 (Copper Contributor)
Solution

@gsquared2 

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.

 

View solution in original post