Count matching cells in date range in excel
In A column, there are dates in mmddyyy format and in B column, there are two variables uses either "PASS" or "FIAIL". All I want to do is to count the "PASS" in individual month range.
Can someone help me in it. I am able to count days of month from the column A but can not link it with Column B.
Answer:
Formula in cell C1:
Copy cell C1 and paste down as far as needed.
Explaining formula in cell C1
Step 1 - Create arrays
=SUMPRODUCT(--(YEAR(A1)=YEAR($A$1:$A$30)), --(MONTH(A1)=MONTH($A$1:$A$30)), --(B1=$B$1:$B$30))
contains three criteria. The first criterion: --(YEAR(A1)=YEAR($A$1:$A$30))
becomes
--(2010=2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010)
becomes
--(TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)
and creates this array: (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
The second criterion: --(MONTH(A1)=MONTH($A$1:$A$30))
creates this array: (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)
The third criterion: --(B1=$B$1:$B$30)
creates this array: (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1)
Step 2 - The product of three arrays
The entire formula now becomes
=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0), (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))
and then
=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)*(1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)*(1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))
becomes
Step 3 - Sum array
=SUMPRODUCT(1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0) equals 7.
Explaining picture of formula in cell C5
In order to show headers I had to adjust formula ranges:
Download excel sample file for this article.
count matching cells in date range.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999
MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)
Related posts:
Formula for matching a date within a date range in excel
Highlight duplicates on same date, week or month using conditional formatting in excel
Excel: How to automatically summarize preceding month and year
Recommended categories
Here is another (slightly shorter) way to write the formula that gets put into C1...
=SUMPRODUCT((TEXT($A$1:$A$30,"mmyyyy")=TEXT(A1,"mmyyyy"))*($B$1:$B$30=B1))
Great work but this is not exactly what I require, I think I was unable to convey my message correctly.
Well, here is it again. Consider the first excel representation with 3 columns A=Date, B=FAIL/PASS & C=count. I don't actually want C with A & B.
In sheet 1, only A & B exist and in sheet 2, it shows like A=Months(January,feb etc) & B=2. Simply it means 2 students pass during January 2010. In sheet 2, there are only 12 cells in A, which are names of months and infront of them, the count of PASS is calculated.
I hope, I have conveyed my message now.
@Janib,
For the count of the passes by month, put this formula next to the January entry on Sheet2 and copy it down...
=SUMPRODUCT((MONTH(Sheet1!A$1:A$1000)=ROW(A1))*(Sheet1!B$1:B$1000="PASS"))
As structured, the formula will handle 1000 data entries on Sheet1... if you need more, just change both 1000's to whatever value you actually might need. If you also want a count of the fails, use the identical formula but change the word "PASS" to "FAIL"