Count number of cells after a time range in one column and before a time range in another column

[swannylfc]

Hi,

I have two columns in the format d/mm/yy hh:mm - e.g. 8/05/17 16:00.

One column - A - is the expiry time of a ticket while the other column - B - is the transaction time of the ticket.

Given a time I enter into C1 (same format as above), I want to count the amount of transactions that expire after C1, and of those I want to exclude any transactions that occur after C1.

I have tried the following formula which is only returning 0.

=COUNTIFS(A1:A100, A1>C1, B1:B100, B1 < C1)

I would love any assistance anyone can provide on this.

Much appreciated and warm regards.

 

[liveinhope]

try this =COUNTIFS(A1:A100, ">" & C1, B1:B100, "< " & C1) . formula in d2 returns 2

[TABLE="width: 553"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]8/05/2017 16:00[/TD]
[TD]6/05/2017 16:00[/TD]
[TD]7/05/2017 16:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]7/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]8/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]9/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]10/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]11/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]12/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]13/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[swannylfc]

try this =COUNTIFS(A1:A100, ">" & C1, B1:B100, "< " & C1) . formula in d2 returns 2

[TABLE="width: 553"]
<tbody>[TR]
[TD]8/05/2017 16:00[/TD]
[TD]6/05/2017 16:00[/TD]
[TD]7/05/2017 16:00[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]7/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]8/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]9/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]10/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]11/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]12/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/05/2017 16:00[/TD]
[TD]13/05/2017 16:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Excellent! Does exactly what I required. Thank you for your prompt and helpful response!