Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

migrated from stackoverflow.com 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: meta.stackexchange.com/questions/22922/… –  sheepez May 28 '12 at 1:17
add comment

2 Answers

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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.