Excel COUNTIF Statement

Question

A---------------------------B
2012-04-12 12:52...... FC
2011-06-24 10:07...... FC
2011-06-24 10:08...... FC
2012-04-17 0:00 ...... COH
2012-04-12 12:22 ...... FC
2011-06-24 10:08......COH
2011-09-06 14:10......COH
2011-09-01 0:00 ...... COH

Goal: To count the amount of "COH" in field B, within a certian timeframe. This example I'd like to try for June 1 2011 - June 30 2011

=COUNTIF(A1:A6, ("<2011-06-30" & ">2011-06-01") & IF(B(At current number) "=COH",1,0)

My problem is trying to get that (At current number), because I want to check the value of D at the current selection.

Ex. When the COUNTIF runs, I want it to execute like this

1. Date is not in range //////////////// Count = 0
2. Date is in range, B != COH ///// Count = 0
3. Date is in range, B != COH ///// Count = 0
4. Date is not in range //////////////// Count = 0
5. Date is not in range //////////////// Count = 0
6. Date is in range, B = COH ////// Count = 1
7. Date is not in range //////////////// Count = 1
8. Date is not in range //////////////// Count = 1

Answer 1

In the first row of column C you could enter:

=IF(AND((A1<DATE(2011,6,30)), (A1>DATE(2011,6,1)),(B1="COH")),1,0)

And then fill all the cells below with this.

Then the sum of all of the values in the C column will be your result.

DATE requires the ends of the date range as (year,month,day)

AND does logical and

---

If you are set on a single function:

=COUNTIFS(A1:A6,"<2011/6/30",A1:A6, ">2011/6/1",B1:B6,"=COH")

should do the trick too.

---

Another answer points out that COUNTIFS requires Excel 2007 or better, and an alternative if it is not available.

Answer 2

Which version of Excel are you using? In Excel 2007 and later versions try using COUNTIFS with this formula in row 1 copied down

=COUNTIFS(A$1:A1,">="&DATE(2011,6,1),A$1:A1,"<"&DATE(2011,7,1),B$1:B1,"COH")

or in earlier versions you can use SUMPRODUCT

=SUMPRODUCT((A$1:A1>=DATE(2011,6,1))*(A$1:A1<DATE(2011,7,1))*(B$1:B1="COH"))