## Count cells based on a condition and month

*Article updated on March 16, 2018*

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 how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 3 Responses to “Count cells based on a condition and month”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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"