Oct 18 2021 07:39 AM
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)
Oct 23 2021 01:53 PM
SolutionThat 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.
Oct 26 2021 07:21 AM
Thank you, @Sergei Baklan! Excel not needing the {} for arrays is what threw me off. Much appreciated!
Oct 23 2021 01:53 PM
SolutionThat 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.