Search code examples
excelexcel-formulatimeline

countfis or sum if array to work out if given time is between the value of two columns

I have two columns in excel with collection(column A) and departure(Column B) times. (images below)

I want to show, in a time line when the resource was active if the given time was between any date time from column A and B

I have tried countifs but that doesnt seem to work as it doesnt match rows, just the columns. something like: =COUNTIFS(A2:A5,">C10",B2:B5,">C10")

I am presuming there is an array formula here that can be used? {=sum(if...}

any help would be appreciated... seasons greetings...

enter image description here

enter image description here

Solution

  • If I understand correctly, you want a 0 or 1 for each of the times in row 10 that you can use a conditional format on to show red or nothing. In A10 try,

    =COUNTIFS($A$2:$A$5,"<"&A$10, $B$2:$B$5,">"&A$10)

    That assumes that the times in B2:B5 are always greater than the associated time in A2:A5. I moved the cell reference outside of the quoted math operator and changed a > to <. Fill right as necessary.

    If actually having the numbers is not important, that formula can be used directly in a conditional format based upon a formula with an Applies to: of $A$10:$Q$10.