CountIf not working

thelad

Board Regular
Joined
Jan 28, 2011
Messages
245
Hi, I have in column H a column that represents times. The format is text format and looks like following:

10:00:00
10:00:00
09:00:00
11:00:00
12:00:00

I want to count the occurrences less than equal too 10:00:00. When I try the following it doesn't work:

=COUNTIF(Sheet1!$H$1:$H$10,"<=10:00:00")

It returns zero when should return 3. However if I just do "=10:00:00" , it will return two as expected. Any ideas on how to get this to work?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
It works for me
change the format of the cell where the formula resides

Book1
H
110:00:00
210:00:00
39:00:00
411:00:00
512:00:00
6
7
8
9
10
11
12
133
Sheet1
Cell Formulas
RangeFormula
H13H13=COUNTIF(Sheet1!$H$1:$H$10,"<=10:00:00")
 
Upvote 0
You will need proper time formats to use countif.
Excel Formula:
=SUMPRODUCT(--(Sheet1!$H$1:$H$10<="10:00:00"),--(Sheet1!$H$1:$H$10<>""))
 
Upvote 0
=COUNTIF(Sheet1!$H$1:$H$10,"<="&TIMEVALUE("10:00:00"))
 
Upvote 0
You will need proper time formats to use countif.
Excel Formula:
=SUMPRODUCT(--(Sheet1!$H$1:$H$10<="10:00:00"),--(Sheet1!$H$1:$H$10<>""))
Hi, This worked, however I was thinking countifs formula as they are two criteria. I didnt mention second criteria as couldnt get Countif to work. Can the formula do the following.

10:00:00 On Time
10:00:00 Late
10:00:00 Holiday
11:00:00 Time
09:00:00 On Time

So basically look for the occurrences of less than equal 10:00:00 minus anything less than equal 10:00:00 and has Holiday in column beside it say. So in the list above the answer would be 3 as the third one is 10:00:00 but its a holiday.

Think need some sort of AND formula.
 
Upvote 0
Hi, This worked, however I was thinking countifs formula as they are two criteria.
Countifs would be better but you will need proper times, not text times. From what I've seen, countifs, sumifs, etc will see numbers that are formatted as text as proper numbers, however there do appear to be anomalies when < or > criteria are involved, as seen with your first formula.

Adding the extra criteria to the sumproduct formula
Excel Formula:
=SUMPRODUCT(--(Sheet1!$H$1:$H$10<="10:00:00"),--(Sheet1!$H$1:$H$10<>""),--(Sheet1!I$1:$I$10<>"Holiday"))
 
Upvote 0
Countifs would be better but you will need proper times, not text times. From what I've seen, countifs, sumifs, etc will see numbers that are formatted as text as proper numbers, however there do appear to be anomalies when < or > criteria are involved, as seen with your first formula.

Adding the extra criteria to the sumproduct formula
Excel Formula:
=SUMPRODUCT(--(Sheet1!$H$1:$H$10<="10:00:00"),--(Sheet1!$H$1:$H$10<>""),--(Sheet1!I$1:$I$10<>"Holiday"))
Sorry meant to reply. I got it work in end but I used minus operator. Your method probably better. Can I ask one last question. I want to further enhance this to identify the number of distinct names I have on my list. I have found formula for Sum product for identifying distinct number but tried to combine to the above and it doesn't work. For Example:

John 10:00:00 On Time
John 10:00:00 Late
Liam 09:00:00 Late
John 10:00:00 Late

I want to identify number of unique names <=10:00:00 that have Late in it. So the answer to this would be two. Although there are three "Late", John is there twice so want to ignore the duplicate.

The following formula will identify number of unique names in column B say but when I try to add the additional conditions we have worked on already including Time and Late column it fails. Any ideas?

=SUMPRODUCT(1/COUNTIF(Sheet1!B2:B510,Sheet1!B2:B510))
 
Upvote 0
Which version of excel are you using (please click the Account Details link in my signature below and update your profile to show this, remember to scroll down and save after making a selection).

With the problems that the text times are causing, I don't think that a unique count is going to work very well with the added criteria unless you have the UNIQUE function available.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top