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, Apr 03 '14 at 07: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, Apr 03 '14 at 07:24
Possible duplicate of http://stackoverflow.com/questions/10158675/count-if-two-criteria-match-excel-formula — John Powell, Apr 03 '14 at 07:24
@JohnSmith You only need to do that to see what you will put in the condition. See my post. — L42, Apr 03 '14 at 07:31

L42 · Accepted Answer · 2014-04-03T09:13:22.250

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 03 '14 at 09:13

answered Apr 03 '14 at 07:38

L42

19,427
11
44
68

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! – Fullmetal_Alchemist_Fan Apr 03 '14 at 07:44
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. – L42 Apr 03 '14 at 07:48
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 Apr 03 '14 at 07:50
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? – Fullmetal_Alchemist_Fan Apr 03 '14 at 08:03
Not by using `CountIfs` I guess. – L42 Apr 03 '14 at 08:40
But ofcourse what you want is possible. See my edit. And again, not by using `CountIfs` but a more complex formula. – L42 Apr 03 '14 at 09:01
1

COUNTIFS is possible, e.g. `=COUNTIFS(A:A,"Item1",B:B,"3/3/2014",C:C,">=14:00",C:C,"<15:00")` – barry houdini Apr 03 '14 at 09:41
@barryhoudini Cool. I was overthinking I guess. :). John forget about the SUMPRODUCT then :) – L42 Apr 03 '14 at 10:00

Using Excel Countifs() with multiple criteria

1 Answers1