Countifs not responding to particular values

**iblameluck78** · 05-02-2013, 05:50 PM

I have a countifs formula that won't work for a few specific values. I have a table listing the day and time of appointments that have taken place, and I want to get a count that matches the days and times (ex: how many appointments took place at 4:00pm on Thursdays, etc.). The good news is that it seems to work for most of the days/times. The bad news is that there are a few buggers who just won't cooperate with me (Ex: 2:30 pm; 4:00 pm; 5:30 pm (there might be more, but I'm not sure)). Does anyone have an idea of why this isn't working for such specific times? I think* that everything is formatted appropriately, and it works for 90% of the entries.

I've attached the file for reference. The table in question is labeled "COUNT" on Sheet1.

*I'm often wrong.

**ConneXionLost** · 05-02-2013, 06:11 PM

Excel does not appear to be recognizing all the appointment times in Sheet1!D3:D23, but as soon as I clicked in one of the uncooperative cells (started edit mode), then hit Enter, the formula worked.

**iblameluck78** · 05-02-2013, 06:19 PM

Well that makes me feel silly. Do you know why this might have happened?

And THANK YOU!

**ConneXionLost** · 05-02-2013, 06:24 PM

This usually occurs due to formatting; either in the cell before the data was added, or the format of the cell where the data was copied. It is a nuisance sometimes recognized as a "feature".

**iblameluck78** · 05-02-2013, 06:32 PM

Is it too late to order the stock model?

**FDibbins** · 05-02-2013, 07:27 PM

If you look at the actual value behond the 2:30:00 PM on sheet1 it is actually...
0.604166666666667
while the actual value behind 2:30:00 on sheet 2 is...
0.604166666666666

If you add a helper column in sheet1 and =ROUNDDOWN(cell_ref,6)...or even 4..., then use this, copied down and across in sheet2...
=COUNTIFS(Appts.!$B$2:$B$1460,E$2,Appts.!$D$2:$D$1460,ROUNDDOWN($D3,6))