COUNTIFS with multiple ANDS and ORS

0
votes

I want to count the number of rows that meet certain criteria, with both AND and OR conditions.

As long as I'm using only AND conditions, it works perfectly. Adding multiple OR conditions gives an #N/A output though.

I can't find the solution anywhere here, have been reading about applying Arrayformula and Range but not sure how to use it.

Any help is much appreciated:

=> Count the records where several AND conditions are met, and at least one of the OR conditions:

=COUNTIFS(K3:K; ">0"; S3:S; "No"; OR(M3:M < 50%; F3:F < 0,9; E3:E > 24))

Thanks!

1
google-sheetslogiccountif

1 Answers

0
votes

Simplifying your requirement (ref. mcve) to counting instances where two columns are mandatory (A and B) and one at least of three others (C, D and E) is obligatory, and where the conditions are all for the presence or not of the column letter, and assuming columns are labelled (and , for delimiter) then perhaps:

  =sumproduct((A2:A="a")*(B2:B="b")*(C2:C="c")+(A2:A="a")*(B2:B="b")*(C2:C<>"c")*(D2:D="d")+(A2:A="a")*(B2:B="b")*(C2:C<>"c")*(D2:D<>"d")*(E2:E="e"))

However it may be easier to flag the relevant rows, say with:

=and(K3>0;S3="No";or(M3<50%;F3<0,9;E3>24))

and then count the flags.