Countif time is 12am

[filcuk]

Hi All,

I spent hours upon hours experimenting with formulas and searching the internet for answer.

I have data range extracted from our company system with date and time in one row and I'm trying to set the formula not to count a cell where the time is 00:00 (no matter what date)

What I tried that doesn't work:

=COUNTIFS(W:W,"<>"&TIME(0,0,0))
returns 0 result every time

=COUNTIF('Due Out'!$S:$S,"<>"&DATE(YEAR('Due Out'!$S:$S),MONTH('Due Out'!$S:$S),DAY('Due Out'!$S:$S))+TIME(0,0,0))
results in super-huge number, I have no idea why

=COUNTIF(W:W,TRUNC(W:W)-W:W&"=0")
this would probably work using array formula, but I wasn't able to make it work


Example data:

[TABLE="width: 111"]
<tbody>[TR]
[TD="align: right"]42288.53[/TD]
[/TR]
[TR]
[TD="align: right"]42288.00[/TD]
[/TR]
[TR]
[TD="align: right"]42288.00[/TD]
[/TR]
[TR]
[TD="align: right"]42288.00[/TD]
[/TR]
[TR]
[TD="align: right"]42288.50[/TD]
[/TR]
[TR]
[TD="align: right"]42289.00[/TD]
[/TR]
[TR]
[TD="align: right"]42293.04[/TD]
[/TR]
[TR]
[TD="align: right"]42293.00[/TD]
[/TR]
[TR]
[TD="align: right"]42293.00[/TD]
[/TR]
</tbody>[/TABLE]
This should result in 3...

 

[shg]

Welcome to the board.

=SUMPRODUCT(--(MOD(W1:W100, 1) = 0))

You don't want to use SUMPRODUCT with a full-column reference.

 

[filcuk]

Hmm, I was trying to use =COUNTIF the whole time, that was my undoing.

I never thought you could use array inside a function this way, it's really smart


Thank you shg

 

[JoeMo]

Hmm, I was trying to use =COUNTIF the whole time, that was my undoing.

I never thought you could use array inside a function this way, it's really smart


Thank you shg

If you don't want to count the 12 AMs I think you need to change = 0 to < > 0 in shg's formula. Then it will return 3 for the data you posted.

 

[filcuk]

If you don't want to count the 12 AMs I think you need to change = 0 to < > 0 in shg's formula. Then it will return 3 for the data you posted.

Thanks for pointing this out!

I was able to produce a working sameple where using text filtering, date filtering and ignoring time 00:00 i can view number of upcomming timed rows!

=SUMPRODUCT(('Due Out'!I2:I9000="Timed")*(TRUNC('Due Out'!S2:S9000,0)>TODAY())*(MOD('Due Out'!S2:S9000,1)<>0))

Excel can be awesome (and frustrating)

 

[shg]

You're welcome.