| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif where condition should be met
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif where condition should be met
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif where condition should be met
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" wrote: 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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
countif where condition should be met
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)) "SCC" wrote: 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" wrote: 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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple condition countif? | Excel Discussion (Misc queries) | |||
| COUNTIF - Condition | Excel Discussion (Misc queries) | |||
| countif and sum if if two condition are satisfied | Excel Worksheet Functions | |||
| COUNTIF - more than one condition | Excel Worksheet Functions | |||
| Compound condition with COUNTIF | Excel Discussion (Misc queries) | |||
Hybrid Mode
