## Count cells based on a condition and month

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)

** **

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

### 3 Responses to “Count cells based on a condition and month”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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"