MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need formula


Posted by Ken on November 09, 2001 12:24 PM

I need a formula in cell G1 that will count the number of values between 40 and 50 in cells A1:F1

Thanks
Ken


Posted by IML on November 09, 2001 12:35 PM

You could use
=SUMPRODUCT((A1:F1>=40)*(A1:F1<=50))
or
=COUNT(A1:F1)-COUNTIF(A1:F1,"<40")-COUNTIF(A1:F1,">50")

Posted by Aladin Akyurek on November 09, 2001 1:16 PM

Ian --

The second must be (which I'd suggest using):

=COUNTIF(A1:F1,">40")-COUNTIF(A1:F1,">60") [ Exclusive Between ]

=COUNTIF(A1:F1,">=40")-COUNTIF(A1:F1,">60") [ Exclusive Between ]

Aladin

Posted by IML on November 09, 2001 1:52 PM

Agreed. Don't know what I was thinking as usual! (NT)


Posted by Mark W. on November 09, 2001 2:48 PM

Yet another approach...

For an inclusive between use...
=INDEX(FREQUENCY(A1:F1,{39.9999999999999,50}),2)

For an exclusive between use...
=INDEX(FREQUENCY(A1:F1,{40,49.9999999999999}),2)