Forum Discussion

anshul-marele's avatar
anshul-marele
Copper Contributor
Dec 23, 2023

What to use in place of "Google Sheets function ARRAYFORMULA" in Excel

Hi,

 

What to use in place of "https://support.google.com/docs/answer/3093275?hl=en" in Excel

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 23, 2023

    anshul-marele 

    If you have Microsoft 365 or Office 2021, every formula in Excel is automatically an array formula, so you can simply omit ARRAYFORMULA. For example,

    =ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))

    becomes

    =SUM(IF(A1:A10>5, A1:A10, 0))

     

    If you have an older version, use

    =SUM(IF(A1:A10>5, A1:A10, 0))

    too, but confirm it by pressing Ctrl+Shift+Enter.

     

    P.S. This is just for illustration; I know it would be easier to use SUMIF or SUMIFS.

Resources