Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question

migrated from May 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:… – sheepez May 28 '12 at 1:17
up vote 1 down vote accepted

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.

share|improve this answer

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


or in earlier versions you can use SUMPRODUCT


share|improve this answer

You must log in to answer this question.