## 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

The YEAR function returns the year from an Excel date.

=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 MONTH function returns a number from 1 to 12 representing the month. Jan = 1, Feb = 2 ... Dec = 12.

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 SUMPRODUCT function multiplies the arrays (product) and then add the numbers in the array and returns a total (sum).

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.

### Picture of formula calculations in cell C5

In order to show headers I had to adjust formula ranges:

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.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 specified values

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

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 unique distinct values in a filtered Excel defined 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

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

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 […]

Count cells between two values

The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]

### 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

Paste image link to your comment.

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