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)
