Count cells with a certain numeric range value

**C Holmes** · 07-17-2014, 12:27 PM

Hi, I need to count the number of cells that contain a number between say 1000 and 1999 and then 2000 and 2999. These are numbers assigned to a specific vehicle type and there are several types. I would like a cell set up to indicate the number of the type of vehicle with numbers assigned between two values. Make sense?

**AlKey** · 07-17-2014, 12:38 PM

Here is an example of how to count numbers with COUNTIFS function

=COUNTIFS(A2:A15,">="&B2,A2:A15,"<="&C2)

	A	B	C	D
2	1000	1000	1999	10
3	1100
4	1200
5	1300
6	1400
7	1500
8	1600
9	1700
10	1800
11	1900
12	2000
13	2100
14	2200
15	2300

**C Holmes** · 07-17-2014, 12:49 PM

first screen.gif

I seem to be a little thick today. What I need is any number in column A between 01000 to 01999 to be counted and insertet in E13 " 01 truck totals"
Can't seem to make it work.

**C Holmes** · 07-17-2014, 12:53 PM

Attachment 332954

I seem to be a little thick today. What I need is any number in column A between 01000 to 01999 to be counted and inserted in E13 " 01 truck totals"
Can't seem to make it work.

**C Holmes** · 07-17-2014, 01:02 PM

Attachment 332954

I seem to be a little thick today. What I need is any number in column A between 01000 to 01999 to be counted and inserted in E13 " 01 truck totals"
Can't seem to make it work. I just get a zero value returned

**AlKey** · 07-17-2014, 01:04 PM

Ok this shows that your numbers are actually text and therefor may required different treatment.

Try this array formula

=COUNT(IF((--(A2:A15)>=--B2)*(--(A2:A15)<--C2),--A2:A15))

	A	B	C	D
2	01000	01000	02000	10
3	01100
4	01200
5	01300
6	01400
7	01500
8	01600
9	01700
10	01800
11	01900
12	02000
13	02100
14	02200
15	02300

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**C Holmes** · 07-17-2014, 01:53 PM

Amazing! that worked, small piece of info I didn't know. Thank you. One more question, when I copy that formula into another cell I want to refer the same array, do I insert $ somewhere?

**AlKey** · 07-17-2014, 02:00 PM

If you copy formula from the formula bar you and the range remains the same than you don't need to.

Thanks you for the feedback!

Count cells with a certain numeric range value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Re: Count cells with a certain numeric range value

Thread Information

Users Browsing this Thread

Similar Threads

how to ignore the numeric cells when giving formula to count cells that contains text only

How to remove only cells with numeric values from range

Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

How do I loop a range of cells until I get a numeric value?

count and arrange cells have numeric values

Bookmarks

Bookmarks

Posting Permissions