Home>


I would like to get the number of records every 10 minutes, 30 minutes, and 1 hour from the diagram above.

You can select 3 types of 10 minutes, 30 minutes, and 1 hour by pulling "Time" from another sheet.
Once selected, the time and count will change.
That is the goal.

Is it necessary to put the data counted once in Excel somewhere? I think, but
Is there only a way to make all patterns in COUNTIFS? I asked a question.

① Is there a method other than COUNTIFS?
(2) Since the numbers are not changed once they are set, there is no need to leave the original data.

Thanks for your response. .

Tool version

Excel 2016

  • Answer # 1

    Although it is a little longer, I wrote it so that the function can be copied down using COUNTIFS.
    〆 Use time and time to count the number of time ranges.
    The time can be selected from 60, 30, and 10 by using a list of validation rules.
    = COUNTIFS ($B $2: $B $9, "<"&F3, $B $2: $B $9, ">="&(F3-G3/24/60), $A $2: $A $9, E3)