Array Formulas with IF statements

Copper Contributor

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

I guess you are working with Google Sheets, correct?

Hello,

 

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

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

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.

Okay - thank you. It's obvious I'm not savvy at either!

This is for what forums for. However, even if we try to help here we need to play a bit with formulas in Excel, and when you apply these formulas to Google Sheets most probably they won't work. Try to follow Ingeborg' advice and ask your question Google Sheets community.