countif criteria sg

[Fred Loh]

i have a column (column B) of cells with numbers in the cells. i want to
count for. example, how many cells have a number between 12 and 29. i used
=countif(B:B,">=12 and <=29") but it did not work. =countif(B:B,">=12,<=29")
also did not work. what is the correct syntax for the criteria in the
formula?

thanks for your help
fred

 

[Gary''s Student]

=COUNTIF(B:B,"<30")-COUNTIF(B:B,"<12")

 

[Don Guillett]

try
=sumproduct((b2:b22>=12)*(b2:b22<=29))

 

[Teethless mama]

=SUM(COUNTIF(B:B,{">=12",">29"})*{1,-1})

 

[Fred Loh]

thanks guys!

Gary's Student's solution works and the logic makes sense.

Teethless mama's solution also works but I don't understand why the need to
multiply {1,-1}

i can't get Don's solution to work. i have a "#NUM!" error.

 

[Don Guillett]

Sumproduct does not take full columns. Must use a range as I did a2:a???

 

[Fred Loh]

It works! Thanks Don.

Sumproduct does not take full columns. Must use a range as I did a2:a???

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help