countif where condition should be met

[SCC]

We track the number of people who log into out computers. I have two countif
statements that I would like to combine. The first counts the number of
logins based on the time of day. There are three different statements.
=COUNTIF('LC371Logins Unique'!L977:L1013,"<12:00 PM")
=COUNTIFS('LC371Logins Unique'!L977:L1013,">12:00 PM",'LC371Logins
Unique'!L977:L1013,"<4:00 PM")
=COUNTIF('LC371Logins Unique'!L977:L1013,">4:00 PM")

The second counts the number of sessions based on the durration. Again,
there are three different statements.
=COUNTIF('LC371Logins Unique'!$O$2:O1265,"<0:15")-COUNTIF('LC371Logins
Unique'!$O$977:O1036,"<0:15")
=COUNTIF('LC371Logins Unique'!$O$2:O1265,">=0:15")-COUNTIF('LC371Logins
Unique'!$O$2:O1265,">0:35")
=COUNTIF('LC371Logins Unique'!$O$2:O1265,">0:35")

What I need to know is how many sessions are less than 15 minutes before
12:00 pm, between 12:00 and 4:00 pm, and after 4:00 pm. This then needs to
be done for sessions between 15 and 35 minutes and for sessions over 35
minutes. I will create separate functions for each question.

How would you combine the two conditions into one function? I know in other
programs I would use a WHERE statement for the second condition. How would I
combine the two conditions in Excel.

Thanks.

 

[Sean Timmons]

Not sure why the durations are in rows 2:1265 and times are in 977:1013?

So, you want to have 9 statements, I asume? Before Noon and < 15 mins,
between noon and 4 and < 15 mins, etc.? First, I notice nothing accounts for
excatly noon or 4, but other than that...

=SUMPRODUCT(--('LC371Logins Unique'!L977:L1265<=12:00 PM),--('LC371Logins
Unique'!$O$977:O1265<0:15))

will return logins before or at 12:00 with a duration less than 15 mins.
Merely change the pieces for the rest of the 9. I picked range of 977:1265
for this. Feel free to adjust as needed, but ensure both ranges in the above
formula are the same (can't do 977 thru 1265 and 2 thru 1265 in the same
formula)

HTH!

 

[SCC]

The formula
=SUMPRODUCT(--('LC371Logins Unique Less 0 Times'!L2:L1322<="4:00
PM"),--('LC371Logins Unique Less 0 Times'!$O$2:O1322<"0:15"))
worked without giving me an error but it merely counted every cell and
returned the result of 1321.

 

[Sean Timmons]

would have helped if I set the lower limit.
Presumign these cells are numbers formatted as time.

=SUMPRODUCT(--('LC371Logins Unique Less 0
Times'!L2:L1322<=.67),(--('LC371Logins Unique Less 0
Times'!L2:L1322>.5),--('LC371Logins Unique Less 0 Times'!$O$2:O1322<.67))