Forum Discussion

erica_tefft's avatar
erica_tefft
Copper Contributor
Jan 03, 2019

Array Formulas with IF statements

Hi,

I'm fairly new to using Array Formulas, and am having some trouble getting a more complex one (for me) to work in a spreadsheet. I have the following formula that I want to be auto-applied to any new row that is added to my spreadsheet. New rows are added frequently via Microsoft Flow, so this is critical.

 

=IF(COUNTIFS(C:C, C2, D:D, D2)>1, "Boat In", "Boat Out")

 

This formula checks columns C and D for duplicate date and boater registration numbers; if a registration number appears twice in one day, the boat is marked as 'in', if it only appears once in a day, it is marked as 'out'.

 

When I apply the Array Formula to this, I can't get it to auto-calculate when a new row is added to the sheet. I think this is because of the 'IF' I have at the beginning of my equation. I attempted to add a clause at the beginning to evaluate for blanks, but this didn't help. In two cases, I got errors, and in another case, everything was calculated to "Boat Out", which is incorrect. Below, I've added the variations I tried with no success:

 

=ARRAYFORMULA(IF(ISBLANK(C2:C), "", IF(COUNTIFS(C:C, C2, D:D, D2)>1, "Boat In", "Boat Out")))

=ARRAYFORMULA(IF(LEN(C2:C)<>0, IF(COUNTIFS(C:C, C2, D:D, D2)>1, "Boat In", "Boat Out")))

=ARRAYFORMULA(IF(C2:C="", "", IF(COUNTIFS(C:C, C2, D:D, D2)>1, "Boat In", "Boat Out")))

 

I'm hoping for some insight as to why this isn't working properly, and what I should try to get it up and running.

 

Thank you in advance!

Erica

6 Replies

  • Hello,

     

    ARRAYFORMULA is not an Excel function. What software are you using? Maybe you can find help in their support forums.

    • erica_tefft's avatar
      erica_tefft
      Copper Contributor

      Yes - from what I've read the functions seem to work similarly between Excel and Google Sheets (other than the way the equation starts).

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        It is similar but it's not the same, the devil is in the detail. In addition and as another example, reference you use as C2:C doesn't work in Excel at all.

Resources