• 503K Members
• 5,592 Online
• 601K Conversations

Highlighted
New Contributor

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)&lt;&gt;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.

Erica

6 Replies

Re: Array Formulas with IF statements

I guess you are working with Google Sheets, correct?

Re: Array Formulas with IF statements

Hello,

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

Re: Array Formulas with IF statements

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

Re: Array Formulas with IF statements

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.

Re: Array Formulas with IF statements

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

Re: Array Formulas with IF statements

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies