Count numbers from 1-150

[Killer17]

Hey all

Need help in creating a formula that will count the number of times a number within the range from 1-150 from Cell D3:F23

The amount should show is 3

Excel Workbook
	D	E	F
3	202	245	199
4	212	193	244
5	216	183	211
6	156	172	182
7	194	185	156
8	203	180	189
9	213	179	188
10	233	244	204
11	192	203	246
12	214	227	180
13	144	210	189
14	158	164	157
15	193	152	201
16	150	180	140
17	225	211	162
18	220	215	201
19	152	155	200
20	189	235	159
21	190	228	187
22	197	224	296
23	187	182	213
Robert

Excel 2007

Thanks

 

[jeffreybrown]

Maybe...

=COUNTIFS($D$3:$F$23,"<151",$D$3:$F$23,">0")

 

[T. Valko]

Hey all

Need help in creating a formula that will count the number of times a number within the range from 1-150 from Cell D3:F23

The amount should show is 3

Excel Workbook
	D	E	F
3	202	245	199
4	212	193	244
5	216	183	211
6	156	172	182
7	194	185	156
8	203	180	189
9	213	179	188
10	233	244	204
11	192	203	246
12	214	227	180
13	144	210	189
14	158	164	157
15	193	152	201
16	150	180	140
17	225	211	162
18	220	215	201
19	152	155	200
20	189	235	159
21	190	228	187
22	197	224	296
23	187	182	213
Robert

Excel 2007



Thanks

One way...

Use cells to hold the criteria:

• A1 = 1
• B1 = 150

Then:

=COUNTIF(D3:F23,">="&A1)-COUNTIF(D3:F23,">"&B1)

 

[Killer17]

Thanks guys both ways work great!

 

[jeffreybrown]

You're welcome...glad it worked for you

 

[snoopyhr]

Not sure if I understood your problem or not, but just an observation. If your data set lower limit is always positive, than you can use this as an alternative. If my observation is not correct then ignore, thanks

=COUNTIF(A3:C23,"<=150") note: use this only if the lowest number is not zero or negative.