Previous Topic Next Topic

How to count the number of occurrences in a range

Closed
STUDENTFJR Posts 2 Registration date Monday October 16, 2017 Status Member Last seen October 17, 2017 - Updated on Oct 19, 2017 at 03:15 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Oct 17, 2017 at 01:24 PM
I would like to know how to count the number of occurrences of each number in a range. Example would be, how many times each number from 1 thru 50 occur in a column. Is there a way to do a range of numbers in the same column and get each numbers occurrence count?

2 responses

Answer 1 / 2
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 16, 2017 at 03:34 PM
STUDENTFJR, Good afternoon.

Suppose:
A1:A1000 --> Your column of data.

If you want to count numbers in a range from 1 to 20, try to do:
C1 --> =COUNTIFS(A1:A1000, ">=1", A1:A1000,"<=20")

If you want to count a specific number, try to do:
D1 --> =COUNTIF(A1:A1000, 32)
To count how many times 32 appears in a range of A1:A1000

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
STUDENTFJR Posts 2 Registration date Monday October 16, 2017 Status Member Last seen October 17, 2017
Oct 17, 2017 at 08:51 AM
The last suggestion is close but what I need is to count every number that that appears in that group of numbers, 1 - 70 say. How many times each number in that range appear in a column in excel. How many times 32 is in the column, how many times 2 appears in the column. and so on. 2 = 14 occurrences, 4=5 occurrences, 20= 5 occurrences........... Thank you for your assistance this is a project I am working on to resolve how many particular part options occur and 6 numbers can be any combination of numbers 1 - 70 for part identification and option for that part. Each number can appear only once in the 6 number combination. Again thank you for your time and quick assistance on this challenge in excel.
Answer 2 / 2
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Oct 17, 2017 at 01:24 PM
STUDENTFJR, Good afternoon.

Could you please save a small example of your spreadsheet, showing what the results would be?

Save the worksheet to a free website, www.sendspace.com and put the download link here.

So our help should be faster and really help you in a conclusive way.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Similar discussions

Excel Macro - Set and autofill a range?
KT -
excel987 -

28 responses
range of numbers
whatsit -
Mazzaropi -

3 responses
Copy range data in a column to new column.
anthonyishere -
anthonyishere -

6 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
Excel Macro/Code help: Creating sheet for each name in column of another sheet
excelnovice2019 -
vcoolio -

21 responses