Counting Values Based on Criteria

**zachharriman** · 03-10-2008, 03:19 PM

I am trying to count the number of Ys by month below. So I need to track how many Ys for Jan, Feb, Mar and so on...
I am looking for a formula that will count them based on the date criteria and will automatically update each time new data is entered.

Date Y/N
1/1/2008 N
1/5/2008 N
1/5/2008 N
1/15/2008 Y
1/17/2008 Y
1/17/2008 N
1/25/2008 Y
1/29/2008 N
2/2/2008 N
2/5/2008 N
2/14/2008 Y
3/1/2008 N
3/6/2008 Y
3/14/2008 N

**VBA Noob** · 03-10-2008, 03:29 PM

Try a pivot table.

Make it dynamic (see link)

http://www.contextures.com/xlPivot01.html

Or a formula Drag down to change months

=SUMPRODUCT((MONTH($A$1:$A$1000)=ROW(A1))*($B$1:$B$1000="Y"))

and

=SUMPRODUCT((MONTH($A$1:$A$1000)=ROW(B1))*($B$1:$B$1000="N"))

VBA Noob

**VBA Noob** · 03-10-2008, 03:49 PM

Example of both attached

VBA Noob

**zachharriman** · 03-10-2008, 05:38 PM

Thanks for your help VBA Noob. I have two more questions. On the Pivot Table, how did you get the "date" to display the Month as opposed to the exact date?
Also, on the solution, how do I chang the Month Formula to recognize the year as well, in case this spreadsheet is used through 2009? Thanks!