Array Formula to omit certain values

Question

I'm currently using the following array formula in B2

=ARRAYFORMULA(SUM(IF(LEN(F15:F114&L15:L114 ), IF((N(F15:F114))+(N(L15:L114 ))>0, 1, 0), )))

The F and L columns referenced are all tick boxes and this formula is counting those boxes that are checked. It allows me to count the F and L tick boxes as one irrelevant of whether 1 of them or both of them are checked.

I'm now looking to expand on this by introducing the ability for the formula to disregard counting the tick boxes of a specific row if the B column of that row states "Scrapped".

So as an example using rows 15 and 16:

• if B15 states "Active" and F15 and L15 are ticked, then that will add one to the count in B2
• however if B16 states "Scrapped" and F16 and L16 are ticked it still won't count towards the count in B2.

To omit certain values in the past I've added <>"" to the formula but can't seem to make that work in this one so any help would be appreciated.

Answer 1

try:

=ARRAYFORMULA(SUM(IF(LEN(F15:F114&L15:L114), 
 IF(((N(F15:F114))+(N(L15:L114))*(B15:B114<>"Scrapped"))>0, 1, 0), )))