help with Countifs

[Llupo01]

Hello all formula experts,

I have quick question. In column "I" I have data with time (numberformat = dd/mm/yyyy hh:mm). I would like to count how many of records are AM and how many PM.

Currently I tried following formula (tried also with ctrl+shift+enter), does not work. If I evaluate criteria 1 with F9, I am getting correct 1/0 values, or true/false without using "--". Can somebody pls help? I believe this will be piece of cake for you

=COUNTIFS(I2:I16,--(TIME(HOUR(I2:I16),MINUTE(I2:I16),SECOND(I2:I16))<TIME(12,0,0)))<time(12,0,0)))[ code]
<time(12,0,0)))[ quote]<time(12,0,0)))<time(12,0,0)))"


I dont know why, but at the end of the formula is missing part, which states "is less than Time(12,0,0)". Dont know, why I cannot post whole formula here. I tried everything, code, quote, nothing works. It always cut my last part.
<time(12,0,0)))[ code]

Thanks in advance.

Brgds,
Tom</time(12,0,0)))[> </time(12,0,0)))[></time(12,0,0)))[>

 

[FormR]

Hi, you could try this:

AM: =SUMPRODUCT(--(MOD(I2:I16,1)<0.5),--ISNUMBER(I2:I16))
PM: =SUMPRODUCT(--(MOD(I2:I16,1)>=0.5),--ISNUMBER(I2:I16))

 

[Llupo01]

Thank you FormR, I want to avoid Supmroduct, because in reality, I have huge range and Sumproduct is way too slow. Sumproduct works for me, already tried...But I would like to know what is wrong with my current formula.

Anyway thank you very much. Any help is appreciated.

 

[Scott T]

To count AM.

=SUMPRODUCT(--(MOD(I2:I16,1) < 0.5),--(I2:I16<>""))

To count PM

=SUMPRODUCT(--(MOD(I2:I16,1)>=0.5))

The forum can bet confused whet you uose the < as it thinks you are using bbcode. put a space around the < and that should fix it.

 

[FormR]

I would like to know what is wrong with my current formula.

Hi, the criteria_range arguments for COUNTIFS() and it's ilk must be ranges.