CODEWITHSUNDEEP
arraysgoogle-sheetsarray-formulas
wilt
wilt

Reputation: 1

Array Formulas - Multiple IF scenarios / Difference between Dates

Here's what I'm struggling with.

I need an arrayformula for the following Google Sheet -

https://docs.google.com/spreadsheets/d/1TcB6MFuOC8EChpZEaJeydw8dfyp6C6EntGqSPGzIc5s/edit?usp=sharing

Using my Raw Data sheet, I am trying to have a column CA show the total # of days between Column BN - Column BE BUT only for rows containing Purch in Column K & ONLY when Closed as a date . I also need it to expand populate for the the whole column CA and to remove blanks.

This is because I am having automated data feed into the spreadsheet via Zapier. Which is also why I need an Array Function so the column doesn't get wiped out in Column CA cells when fresh data gets inputted.

I know this is way off, but this is about as far as I've gotten since I can't find any tutorials about this.

=ArrayFormula(if($K1:$K="Purch")*((MINUS{$BN,$BE)))

Upvotes: 0

Views: 329

Answers (1)

Aresvik
Aresvik

Reputation: 4620

Try this in cell CA2:

=arrayformula(if(BN2:BN<>"",if(K2:K<>"",BN2:BN-BE2:BE,),))

or this in cell CA1 (keeping CA2:CA blank):

=arrayformula({"Avg Closed Days";if(BN2:BN<>"",if(K2:K<>"",BN2:BN-BE2:BE,),)})

If you only need to evaluate when BN has a value, use this:

=arrayformula({"Avg Closed Days";if(BN2:BN<>"",BN2:BN-BE2:BE,)})

If it helps, please consider https://stackoverflow.com/help/someone-answers :-)

Upvotes: 1

Related Questions