Using Excel Countifs() with multiple criteria

Question

I'm attempting to use the countifs() function, however, I keep returning a value of 0. This is the dataset:

   |   A  |    B  |      C     |
   | item1| 3/3/14| 12:00:15 AM|
   | item2| 3/3/14| 12:00:39 AM|
   | item4| 3/3/14| 12:05:19 AM|
   | item1| 3/3/14| 12:05:19 AM|

I tried the following countifs() to count all the items that fit this criteria:

=countifs(A:A,"item1",B:B,"3/3/14",C:C,"12:*")

So I want this function to grab "item1" in column A, 3/3/14 in B, and anything that starts with "12:". So this should return a value of 2, but it's not. Any ideas?

Your data are all text? or Actual date and time? That is the key. Try formatting it as General then use the value the cell displays in your Countifs. — L42
@L42 If I convert my times to general then it looks like 0.0334234 and I can't really make sense of that. — Fullmetal_Alchemist_Fan
Possible duplicate of stackoverflow.com/questions/10158675/… — John Powell
@JohnSmith You only need to do that to see what you will put in the condition. See my post. — L42

L42 L42 · Accepted Answer · 2014-04-03T07:38:35

To explain my comment, refer to below:

Sample Data

As you can see, I have time values in C7:C8 and D7:D8 respectively in different formats.
To get what you want, it's like basically counting all entries less than 01:00:00 AM.
So to get your formula to work, use this formula instead:
=COUNTIFS(A$2:A$5,"Item1",B$2:B$5,"3/3/2014",C$2:C$5,"<" & 0.041667)

Edit1: As commented, you can use reference cell as well. In above, it is C8 or D8.

=COUNTIFS(A$2:A$5,"Item1",B$2:B$5,"3/3/2014",C$2:C$5,"<" & $C$8)

Edit2: As commented, what you want is possible but not using CountIfs

sumproduct

Above we used this:
=SUMPRODUCT(1*(A2:A9="Item1"),1*(B2:B9=DATEVALUE("3/3/2014")),1*(C2:C9>TIMEVALUE("2:00:00 PM")),1*(C2:C9<TIMEVALUE("3:00:00 PM")))

We use the SUMPRODUCT function.
A bit more complex formula for a bit more complex requirement of yours. :)

Using Excel Countifs() with multiple criteria

1 Answers