0

I have this formula pasted below that works in Google Sheets, but not in Excel. I've looked up possible solutions, but just cannot find what may be the problem with it. The return I get is just the formula or if I break the formula down further I get #NAME?

=UNIQUE(FILTER('Cost of Sale'!A5:E, 'Cost of Sale'!A5:A<>"N/A", 'Cost of Sale'!A5:A<>('Materials'!A4), 'Cost of Sale'!A5:A<>('Materials'!D4), 'Cost of Sale'!A5:A<>('Materials'!G4), 'Cost of Sale'!A5:A<>('Materials'!J4), 'Cost of Sale'!A5:A<>('Materials'!M4), 'Cost of Sale'!A5:A<>('Materials'!P4), 'Cost of Sale'!A5:A<>('Materials'!S4), 'Cost of Sale'!A5:A<>('Materials'!V4), 'Cost of Sale'!A5:A<>('Materials'!Y4), 'Cost of Sale'!A5:A<>('Materials'!AB4), 'Cost of Sale'!A5:A<>('Materials'!AE4), 'Cost of Sale'!A5:A<>('Materials'!AH4), 'Cost of Sale'!A5:A<>('Materials'!AK4), 'Cost of Sale'!A5:A<>('Materials'!AN4), 'Cost of Sale'!A5:A<>('Materials'!AQ4), 'Cost of Sale'!A5:A<>('Materials'!AT4), 'Cost of Sale'!A5:A<>('Materials'!AW4), 'Cost of Sale'!A5:A<>('Materials'!AZ4), 'Cost of Sale'!A5:A<>('Materials'!BC4), 'Cost of Sale'!A5:A<>('Materials'!BF4), 'Cost of Sale'!A5:A<>('Materials'!BI4), 'Cost of Sale'!A5:A<>('Materials'!BL4), 'Cost of Sale'!A5:A<>('Materials'!BO4), 'Cost of Sale'!A5:A<>('Materials'!BR4)))

3
  • Welcome to Web Applications. Google Sheets and Excel haven't a 100% compatibility actually recent Excel versions aren't 100% compatible with older versions. What Excel version are you using? Have you already reviewed the Excel list of functions for your version? Jan 16, 2022 at 17:51
  • The UNIQUE and FILTER functions were introduced in Office 365 and do not exist in prior versions of Excel. So if you don't have a cloud subscription, those functions are not available. The further back in versions you go, the less Excel has in common with Google Sheets. (In addition, I will note that even in Google Sheets, your formula could have been written much more succinctly.)
    – Erik Tyler
    Jan 16, 2022 at 18:26
  • This is with the Office 365 version. And yeah I figured it definitely could be done more succinctly. I will continue to try to figure out how to do this with Office 365.
    – plet9090
    Jan 16, 2022 at 20:37

1 Answer 1

1

Turns out Excel does not like A5:A. A needs to be defined. A5:A1000 will work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.