Formulas

Question 1

count cells between dates

Answer 1

=COUNTIFS(range,”>=”&date1,range,”<=”&date2)

Question 2

Count cells between two numbers

Answer 2

=COUNTIFS(range,”>=X”,range,”<=Y”)

Question 3

Count cells equal to

Answer 3

=COUNTIF(range,value)

Question 4

Count cells equal to case sensitive

Answer 4

=SUMPRODUCT((–EXACT(value,range)))

Question 5

Count cells equal to either x or y

Answer 5

=COUNTIF(rng,value1) + COUNTIF(rng,value2)

Question 6

Count cells equal to one of many things

Answer 6

=SUMPRODUCT(COUNTIF(rng,things))

Question 7

Count cells greater than

Answer 7

=COUNTIF(rng,”>X”)

Question 8

Count cells less than

Answer 8

=COUNTIF(rng,”

Question 9

Count cells not equal to

Answer 9

=COUNTIF(rng,”<>X”)

Question 10

Count cells not equal to many things

Answer 10

=COUNTA(range)-SUMPRODUCT(COUNTIF(range,things))

Question 11

Count cells not equal to x or y

Answer 11

=COUNTIFS(rng,”<>x”,rng,”<>y”)

Question 12

Count cells over 100 characters

Answer 12

=SUMPRODUCT(N(LEN(range)>100))

Question 13

Count cells that are blank

Answer 13

=COUNTBLANK(rng)

Question 14

Count cells that begin with

Answer 14

=COUNTIF(rng,”txt*”)

Question 15

Count cells that contain either x or y

Answer 15

=SUMPRODUCT(–((ISNUMBER(FIND(“abc”,rng)) + ISNUMBER(FIND(“def”,rng)))>0))

Question 16

Count cells that contain errors

Answer 16

=SUMPRODUCT(–ISERR(range))

Question 17

Count cells that contain five characters

Answer 17

=COUNTIF(rng,”?????”)

Question 18

Count cells that contain negative numbers

Answer 18

=COUNTIF(rng,”<0”)

Question 19

Count cells that contain numbers

Answer 19

=COUNT(rng)

Question 20

Count cells that contain odd numbers

Answer 20

=SUMPRODUCT(–(MOD(rng,2)=1))