COUNTIF [hh]:mm format.

[Newbzie]

Im trying to write a =COUNTIF how many times a specifik time occurs.

So the time I'm looking for is 07:45.

I tried writing =COUNTIF(C4:L4, 07:45:00), but get an error.
I also tried =COUNTIF(C4:L4, TIME(7,45,0)), but same error.

Anyone know what I could do here?

[jason.b75]

Could be a floating point issue.

Try

=SUMPRODUCT(--(MROUND(C4:L4,time(0,0,1))=TIME(7,45,0))

You might need to array confirm that with Shift Ctrl Enter.

[pjwhitfield]

Try

Please Login or Register  to view this content.

Also you could use the decimal value

Please Login or Register  to view this content.

[Newbzie]

Figured out part of it.

Apperently my Excel doesn't have a "=COUNTIF" function, being danish it's called "TÆL.HVIS"... Also the syntax is with a ";" between Range and Criteria.

Now my next problem is that I wan't to count over 4 different ranges, C4:L4;P4:Y4;C20:L20;P20:Y20, but the seprator being a ";" thats not easy, lol... Sux to be a newbie at things hehe.

[jason.b75]

Also you could use the decimal value

Please Login or Register  to view this content.

Which is no differnt to the formula which we have already been informed doesn't work =COUNTIF(C4:L4,TIME(0,45,0))

The fact that that formula caused an error suggests sme kind of underlying issue.

@Newbzie, could you be more specific on your meaning of 'get an error' please. In excel terminology, an error and an incorrect result are 2 completely different things.

[jason.b75]

In that case

=TÆL.HVIS(C4:L4;"07:45")+TÆL.HVIS(P4:Y4;"07:45")+TÆL.HVIS(C20:L20;"07:45")+TÆL.HVIS(P20:Y20;"07:45")

If you need more that 4 ranges then a less conventional method maybe required.

For example:-

=SUM(TÆL.HVIS(FORSKYDNING(C4;{0.16};{0;13};1;10);"07:45"))

[pjwhitfield]

You'll need to do multiple COUNTIFs

eg

Please Login or Register  to view this content.

However, are there time values in the ranges not covered by the ranges you want? ie C5:L19 and M20:X20 ? If there is no chance for entries in there to throw it out then you could simply do one COUNTIF (or TÆL.HVIS) covering the full range

Please Login or Register  to view this content.

it does however come with risks of some other data affecting it.

[Newbzie]

This worked, thank you

And thanks all for the help, even confirming that it should have worked, made me think that perhaps the command (COUNTIF) was the issue hehe