Forum Discussion
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.
- SergeiBaklanDiamond Contributor
I guess you are working with Google Sheets, correct?
- erica_tefftCopper 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).
- SergeiBaklanDiamond Contributor
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.