Geek Answers Handbook

Charts with complex criteria

Is it possible to place a complex condition count()/countif()in Excel? By "difficult" I mean something that involves and, orand other logical operations, and combinations thereof. The simplest case is only andor or. For example, I would like to count these cells with a value less than 3 or the value is equal false.

-1
source share
2 answers

In countif(or countifs) a transaction ORcan be calculated by adding, for example.

=COUNTIF(range,"<3")+COUNTIF(range,FALSE)

A transaction ANDcannot be easily processed in countif, but can be controlled either by countifs, or SUMPRODUCT, for example

=COUNTIFS(range,"<3",range,">1")

=SUMPRODUCT((range<3)*(range>1)*1)
+2
source

, COUNTIF , OR. , range2, "a" "b"

=COUNTIF(range2,"*a*")+COUNTIF(range2,"*b*")

, "ab".

=COUNTIF(range2,"*a*")+COUNTIF(range2,"*b*")-COUNTIFS(range2,"*a*",range2,"*b*")

(.. , N (AuB) = N (A) + N (B) -N (AnB)). , ( OP, N (AnB) = 0).

+2

More articles:


All Articles