Countif for a range of values

[Reggie]

Hello - It's the incompetent one asking for help yet
again.

I'm trying to count the number of items in a range that
fall between 6 and 12, inclusive.

Jon Walkenbach left that one out of his book.

Any suggestions?

 

[JulieD]

Hi Reggie

how about
=COUNTIF(A1:A10,">=" & 6)-COUNTIF(A1:A10,">"& 12)

Cheers
JulieD

 

[JE McGimpsey]

Or, just a slightly shorter variation:

=COUNTIF(A1:A10,">=6") - COUNTIF(A1:A10,">12")

alternatively:

=SUMPRODUCT(--(A1:A10>=6),--(A1:A10<=12))

see

http://www.mcgimpsey.com/excel/doubleneg.html for the usage of --

 

[Reggie]

Hello Ms. Julie.

I finally used this one:

=SUM(COUNTIF(H11:H143,{6,7,8,9,10,11,12}))

Of course, if the range gets much bigger, I'm in a world
of hurt. I'll save your response if that ever happens!

Thanks for answering! Ciao!
Reg

 

[Reggie]

Hey - even better!

Thanks!

Reg

-----Original Message-----
Or, just a slightly shorter variation:

=COUNTIF(A1:A10,">=6") - COUNTIF(A1:A10,">12")

alternatively:

=SUMPRODUCT(--(A1:A10>=6),--(A1:A10<=12))

see

http://www.mcgimpsey.com/excel/doubleneg.html for the usage of --



.

 

[JulieD]

Hi Reggie

thanks for the feedback ... glad you found a solution

Cheers
JulieD