# Excel COUNTIF Statement

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
-

## migrated from stackoverflow.comMay 28 '12 at 8:38

This question came from our site for professional and enthusiast programmers.

Non-programming q is offtopic. Should be asked at Super User – brettdj May 27 '12 at 5:31
It seems a bit like the jury is out on whether or not this doesn't count as programming: meta.stackexchange.com/questions/22922/… – sheepez May 28 '12 at 1:17

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.

-

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"))`

-