0

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:

excel rows with time specified

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)")

Improve this question
7
  • You count ages but you use a column with time-values? Can you explain a bit more how you derived your desired results? Commented Aug 15, 2022 at 8:40
  • sorry for confusing, the second one (age) was an another example of what I'm looking for. Commented Aug 15, 2022 at 9:06
  • Please can you edit your question to sort this out. You are showing desired results that do not belong to the give formula you have shown. Please do not confuse your readers. Commented Aug 15, 2022 at 9:08
  • @JvdV ok sure and done! Commented Aug 15, 2022 at 9:23
  • No you have not. The table does not match the formula you mentioned. This is utterly confusing. Commented Aug 15, 2022 at 9:24

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

here is the same solution and seems it should be correct but I don't know why it doesn't work for me :( got-it.ai/solutions/excel-chat/excel-tutorial/date-and-time/…
@SaeedSepehr, what is your output when you try VALUE(C2). Are your values actual time-values and not dates (integers with decimals) formatted to only show times?
@JvdV its 0.628472222
Then the answer given here is absolutely correct.
Are your upper and lower bounds correctly defined ? In you example (i.e. =COUNTIFS(C:C,"AND(<00:00, >2:00)")), it counted times inferior to 00:00 and superior to 2:00, which is a null set and returns 0. Alternatively, depending from where you are you might want to try =COUNTIFS(C:C;">00:00";C:C;"<2:00") .
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.