&COUNTIF working for some cells and not others I created a table to keep track o

Question

&COUNTIF working for some cells and not others

I created a table to keep track of some inventory. One of the values I wanted to count was the time at which the inventory was delivered. I did this to see a trend or/and pattern in the most common time when inventory needs to be replaced.

The time delivered cell is a drop down list from 07:00:00 AM to 07:00:00 PM in increments of 15 minutes. The format for the cell is set at time: ##:##:## AM/PM

The formula I have for the count (in a separate cell) is: =COUNTIF(B7:AF53,"11:00:00 AM")

This same formula works with 11:15:00 AM time or 10:45:00 time, but it does not work with the 11:00:00 AM time. This also happens with 12:45:00 PM time and others. The time values that it does not work with is random.

I have tried adding * like this: =COUNTIF(B7:AF53,"*11:00:00 AM*") but that does not seem to work either. I don't understand why it works with some times and with others it doesn't.

For example 11:15:00 AM works perfectly: =COUNTIF(B7:AF53,"11:15:00 AM")

Anyone have any ideas?

Explanation / Answer

%(*#&% You could try treating the times as values and seeing if the difference is reasonably close to zero:

=SUMPRODUCT(--(ABS(B7:AF53-TIME(11,0,0))<1.0E-8)).

Or, if you have the time you want to compare to in a cell somewhere, you can use a cell reference:

=SUMPRODUCT(--(ABS(B7:AF53-$A2)<1.0E-08))

where $A2 holds the time "11:00:00 AM" as a value (not just as a text string).

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.