0

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!

nannev
  • 27
  • 1
  • 7

1 Answers1

0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Thanks for your reply, I used to flag the rows and then count but as I want to divide my database into x segments, that meant adding x rows in which I flag on that condition, which can be quite time-intensive and not a very clean solution. I can't get the first code to work though, could you pls. apply it to my specific code? – nannev Jun 26 '17 at 06:26