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

8/05/2017 16:00	6/05/2017 16:00	7/05/2017 16:00	2
8/05/2017 16:00	7/05/2017 16:00
8/05/2017 16:00	8/05/2017 16:00
8/05/2017 16:00	9/05/2017 16:00
8/05/2017 16:00	10/05/2017 16:00
8/05/2017 16:00	11/05/2017 16:00
8/05/2017 16:00	12/05/2017 16:00
8/05/2017 16:00	13/05/2017 16:00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[swannylfc]

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

8/05/2017 16:00	6/05/2017 16:00	7/05/2017 16:00	2
8/05/2017 16:00	7/05/2017 16:00
8/05/2017 16:00	8/05/2017 16:00
8/05/2017 16:00	9/05/2017 16:00
8/05/2017 16:00	10/05/2017 16:00
8/05/2017 16:00	11/05/2017 16:00
8/05/2017 16:00	12/05/2017 16:00
8/05/2017 16:00	13/05/2017 16:00

<tbody>
</tbody>

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