[suspectx0]

All right, I am a slight novice at excel. I feel like I know a lot about it but the subject I'm trying to figure out is troubling me.
I have an excel worksheet that in collum a I will have dates. And collum be I will have a drop down box that will allow you to click yes or no. so what I want is to be able to somehow show how many times someone says yes in a certain month.
Ex: in collumn a I have 3 months worth of dates (november, January, February,i.e. 1/1/08,with some dates missing ). Then in collumn be I have a few yes's and 8 no's. And I want to be able to show in another part of the sheet how many times someone says yes in the month of January. I have been looking around the internet at nested ifs and arrays but I can't completely figure out how to use them in my cercumstances. As of right now I have my spreadsheet split up and just am using countif command and having the range a dedicated range (b2-b80) for one month then b81-b100) for a nother. I was wondering if there was a way so that if I put any date in any place on collumn A it would recognize it and know to add that date if it was a yes

[JBeaucaire]

Simple enough, but it uses the SUMPRODUCT to get the effect you want. You are basically multiplying the number of times ONE TRUTH is found in Column A by a SECOND TRUTH found in COLUMN B.

First, you have to know how to tell what month is in each cell in Column A. That function is MONTH(reference).

For instance, if the date in cell A2 is 1/4/2008, then the formula =MONTH(A2) would respond 1... 1 means January. 2 means February... etc.

Since you now can test the month, you can do a test to find only cells that return a value of 1 (January).

Next, you're going to find only the ones that are both 1 (January) and "yes" in the adjacent B column. Here's the initial formula... it's weird:

=SUMPRODUCT((MONTH(A2)=1)*(B2="yes"))

This will work because I made it true and only did one row. Now let's expand it to:

=SUMPRODUCT((MONTH(A2:A4)=1)*(B2:B4="yes"))

See the picture below. I put that formula into cell E2 and after filling in several entries, it works swimmingly.

Lastly, you wanted to be able to put in as many dates as you want and check for every month's YES count. The easiest way to make the that happen is to use LABELS.

To use a label highlight as much of the A column as you think you'll ever use... perhaps 100 cells. Notice the picture again, where you see "C2" in a white box above the A column, that's the NAME field. Type in the name DATES and press Enter.

Now highlight the same number of cells in the B column, then name that group VOTES.

Now you can replace the formula in cell E2 with:

=SUMPRODUCT((MONTH(Dates)=1)*(Votes="yes"))

Notice the A2:A4 was replaced with the word DATES? That's because it is now usable as a reference to the named range of cells called DATES. Nifty! It also makes the formula easier to read, you can look at it and tell what's going on. Names are great that way.

Anyway, to get the count for February, just change the 1 to a 2. March would be 3.

See the attached file example and look at the finished formulas in E2 F2 G2. You should get the idea.

Don't jump to the end. Do what I've done one step at a time so you understand each segment of the problem. You don't even have to do the LABEL thing at all, I do it because it makes for clean looking formulas.