• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count occurrances in month and year given certain criteria?

T

Tarheel8181

New Member
Hi,

I'm sure this is an easy question, but I'd appreciate any help anyone might be able to offer.

I have dates (mm/dd/year) spanning several years in column A of my data set. Only if column B meets a particular condition (in this case, it has "A" associated with the record), I want to count the number of occurrences from January 2013. I want a formula that will give me an answer of 2. I have used a sumproduct function, but I can only get this to return the total number of dates falling within Jan 2013 (3). File attached.
 

Attachments

Hi ,

Change your formula to :

=SUMPRODUCT(($A$7:$A$9>=$B$1)*($A$7:$A$9<=$B$2)*($B$7:$B$9="a"))

Another possibility is :

=SUMPRODUCT((TEXT($A$7:$A$9,"mmmm")=$B$1)*($B$7:$B$9="a"))

where B1 has a drop-down with the 12 month names ( January through December ) , from which the user can select any one month.

Narayan
 
Thanks! I changed my formula to the first option you suggested, but I'm getting a #Value! error. Any idea what might be causing that?
 
You must log in or register to reply here.
Back
Top