1

I have an excel sheet with 2 columns

Column A = "CA", "CR" or "IN"

Column B = Date & time format DD/MM/YYYY HH:MM

I want to make a count at the bottom of a column for each row that has this criteria:

i) Row 1-8 = "CA"

ii) Row 1-8 needs to check for a time range, namely > "17:00" and < "04:59"

This is what I've come up with so far:

=COUNTIFS(A2:A8,"CA",B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)>"04:59"), B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)<"17:00")

I presume using a range within a the text function is wrong, but don't know how to resolve this.

Because Column B is in a date and time format, I'm having to change it to a string within the function so I can make a test on just the time - Maybe there's a better way?)

Thanks

Mark Banin
  • 21
  • 3

3 Answers3

3

The problem with COUNTIFS is that you can't manipulate the conditions a lot. You could insert a column containing the time only, in which case you could use COUNTIFS but if you cannot, you can use SUMPRODUCT for substitute:

=SUMPRODUCT((A1:A6="CA")*(((TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0))+(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)))>0))

This applies a few conditions on an example range A1:B6:

(A1:A6="CA") that ensures that the row has CA,

(TEXT(B1:B6,"hh:mm")*1>TIME(17,0,0)) that ensures that the time is above 17:00

(TEXT(B1:B6,"hh:mm")*1<TIME(5,0,0)) that ensures that the time is before 05:00 (You have 4:59 in your question, if you really meant less than 4:59 then change this part).

The + for the last two conditions 'OR's the two conditions, then the whole thing is 'AND'ed with the first condition.

Jerry
  • 70,495
  • 13
  • 100
  • 144
1

This is difficult to do with COUNTIFS because you can't modify ranges with functions.....but you can do that with SUMPRODUCT - try this

=SUMPRODUCT((A2:A8="CA")*(MOD(B2:B8,1)<"17:00"+0)*(MOD(B2:B8,1)>"04:59"+0))

I'm assuming that you want between 04:59 and 17:00 - in your point ii) you show it the opposite way to what you have in your formula

MOD extracts the time from the date/time so it can be compared against a time range. If you are counting within a range including whole hours, e.g. 5 to 16 inclusive you can use HOUR function without MOD, i.e.

=SUMPRODUCT((A2:A8="CA")*(HOUR(B2:B8)>=5)*(HOUR(B2:B8)<=16))

barry houdini
  • 45,615
  • 8
  • 63
  • 81
1

You do not need to deal with strings at all for the time. Excel provides with quite a few date&time functions, enough in your case.

A concise formula is

=SUMPRODUCT((A1:A8="CA")*(MOD(B1:B8,1)<=TIME(17,0,0))*(MOD(B1:B8,1)>TIME(4,59,0)))

Notes:

  1. I assume you meant <=17:00, instead of <17:00. It is easy to modify the formula if I was wrong.
  2. You used A2:A8, but you probably wanted A1:A8, as per the text. It is easy to modify the formula if I was wrong.
  3. COUNTIFS is usually much less flexible than SUMPRODUCT (combined with other functions as MATCH, INDEX, SUM, etc.)

PS: as barry houdini points out, the OP asks for two opposite time ranges. I have chosen here one of them.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • ....but according to the question B1:B8 contains "date & time" so this won't work - you need to separate the time from the date as Jerry's solution (and mine) do.....or use a function which is "date neutral" like HOUR – barry houdini Jan 05 '15 at 13:45
  • @barryhoudini - Thanks to for pointing out the need for getting rid of the date. Your use of `HOUR` is a good option. I use `MOD` here, it is an equally valid option. – sancho.s ReinstateMonicaCellio Jan 05 '15 at 14:26