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, Commented Apr 3, 2014 at 7:22
@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
– Fullmetal_Alchemist_Fan, Commented Apr 3, 2014 at 7:24
Possible duplicate of stackoverflow.com/questions/10158675/… — John Powell
– John Powell, Commented Apr 3, 2014 at 7:24
@JohnSmith You only need to do that to see what you will put in the condition. See my post. — L42
– L42, Commented Apr 3, 2014 at 7:31

L42 · Accepted Answer · 2014-04-03 09:13:22Z

2

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. :)

edited Apr 3, 2014 at 9:13

answered Apr 3, 2014 at 7:38

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Fullmetal_Alchemist_Fan Over a year ago

Dude, so awesome! I can't thank you enough, man! I've been beating my head against the wall on this one! :) What if I wanted to get times in between 12:00:00 AM & 1:00:00 AM? Sorry for the extra question, but it would be super helpful!

L42 Over a year ago

That's exactly what the formula does. Since you are counting all that is < 0.04167. Equivalent of 12:00:00 AM is 0 and 1:00:00 AM is 0.04167. To increase precision, increase the decimal place like this 0.0416666666666667 or use a reference cell to put the 1:00:00 AM value.

Fullmetal_Alchemist_Fan Over a year ago

Well I guess to better rephrase my question is what if I wanted to grab like times between 2:00PM & 3:00PM and other time intervals? Salamat po!

Fullmetal_Alchemist_Fan Over a year ago

I see that I can reference the cells, but I have a very, very large dataset and so I'll have to use specific times intervals. Is there actually a way to do this?

barry houdini Over a year ago

COUNTIFS is possible, e.g. =COUNTIFS(A:A,"Item1",B:B,"3/3/2014",C:C,">=14:00",C:C,"<15:00")

|

Collectives™ on Stack Overflow

Using Excel Countifs() with multiple criteria

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related