I want to count number of values in different ranges in an Excel column.
Example 1: Imagine I have some data in 40 rows, each one happened in different time of day. Like the below image:
now I want to count number of row repeated in different ranges, for example, count number of rows that have time between 12:00 to 18:00, again count 18:00 to 00:00, and more to 11:59 (next 12:00)
| Time range | Count |
|---|---|
| 00:00 to 6:00 | ? |
| 06:00 to 12:00 | ? |
| 12:00 to 18:00 | ? |
| 18:00 to 23:59 | ? |
Finally I have a table with 4 rows that shows how many row I have in those ranges and I can create a chart by that.
Example 2: Count people based on age range. result would be like this:
| Age range | Count |
|---|---|
| 12 to 18 | 3 |
| 18 to 25 | 5 |
| 25 to 35 | 4 |
| 35 to 45 | 1 |
| 45 to 60 | 2 |
P.S:
I used countif with logical AND, but it didn't work. like this: =COUNTIFS(C:C,"AND(<00:00, >2:00)")
CodePudding user response：
A more correct use of COUNTIFS (which is different from COUNTIF), would be :
'Counts values strictly between 00:00 and 2:00
=COUNTIFS(C:C,">00:00",C:C,"<2:00")
Hope it helps