Counting with Multiple Criteria (including Date Ranges and Text)

Hi. I have been a user of this forum for a while now but have only just signed up. I have a fairly tough question.

In cell B10 I have "'01/09/12" (note the ' so it is appearing as text not a date) - this is the start date.
In cell B11 I have "'30/09/12" (as above) - this is the end date.

So my date range I want to work with is 01/09/12 - 30/09/12

In cells D50:D123 I have a whole list of different actual (without ' ) dates.
In cells E50:E123 I have different words (these are "YES", "NO" and "MAYBE")

In one cell (say A1) I want to count how many times a date within my range (01/09/12 - 30/09/12) appears in D50:D123 so I use the following which works perfectly.
=SUMPRODUCT(--($D$50:$D$123>=DATEVALUE(B10)),--($D$50:$D$123<=DATEVALUE(B11)))

However (and this is where I have problems, in a cell (say A2) I want to count how many times how many times a date is within my range (in cells D50:D123) only if it has "YES" in the cell next to it (in cells E50:E123).

Example #1:
D55 has "09/09/12"
E55 has "YES"
A2 counts 1

Example #2:
D55 has "09/09/12"
E55 has "NO"
A2 counts 0

If anyone has any ideas, please let me know. Thanks.

I think all you need is to add a 3rd criteria in your SUMPRODUCT...

=SUMPRODUCT(--($D$50:$D$123>=DATEVALUE(B10)),--($D$50:$D$123<=DATEVALUE(B11)),--($E$20:$E123="YES"))

Does this work for you?

Thanks for that djapigo, that worked perfectly.

FDibbins the reason I am stealing clear of COUNTIFS (which do seem a lot easier) is:
1. I am finding it hard to link the "criteria" to a particular cell - it seems as though I have to actually include my criteria as part of the formula. I can't say (for example):
=countifs(A1:A5,>=B1)
2. The formula was originally done as SUMPRODUCT, so I am continuing on with this.

Anyhow, it seems to be working. I do have a follow up question which I should has asked originally. Instead of having it limited to "YES" only I would like to have it pickup a "MAYBE" also. So I believe I have to incorporate an OR function into the formula. Currently as it stands:

=SUMPRODUCT(--($D$50:$D$123>=DATEVALUE(B10)),--($D$50:$D$123<=DATEVALUE(B11)),--($E$20:$E123="YES"))

Any ideas?

Thanks

Hi GABBA,

Try this...
=SUMPRODUCT(--($D$50:$D$123>=DATEVALUE(B10)),--($D$50:$D$123<=DATEVALUE(B11)),--($E$20:$E123="YES"))+SUMPRODUCT(--($D$50:$D$123>=DATEVALUE(B10)),--($D$50:$D$123<=DATEVALUE(B11)),--($E$20:$E123="MAYBE"))

As for FDibbins' suggestions which I totally agree is much easier to use, the correct formula would look something like this...
=countifs(A1:A5,">="&B1) you have to quote the >= symbol then concatenate (&) B1

Thanks djapigo. If I were to go down the COUNTIFS rout, how would I incorporate the OR function so that I can include either a "YES" or a "MAYBE" (similar to how I want it with the SUMPRODUCT)

Thanks.

Same as above... you simply ADD the 2 formulas...