COUNTIFS Between Date Range

Hi All,

So I have been working on quite an extensive formula and thanks to some help on here I was able to get the formula working as intended. However, I have now gotten an additional request to have the option to select a date range.

My existing formula is:
=COUNTIFS(data_source[Period],IF($S$1="-- Period --","<>",$S$1),data_source[Month],IF($S$2="-- Month --","<>",$S$2),data_source[Division],IF($C$3="-- Division --","*",$C$3),data_source[Cost Centre],IF($G$3="-- Cost Centre --","<>0",$G$3),data_source[Manager],IF($K$3="-- Manager --","*",$K$3),data_source[Job Description],IF($O$3="-- Job Description --","*",$O$3),data_source[Employment Type],IF($S$3="-- Employment Type --","*",$S$3),data_source[Gender],IF($C$4="-- Gender --","*",$C$4),data_source[Race],IF($G$4="-- Race --","*",$G$4),data_source[Age Group],IF($K$4="-- Age Group --","*",$K$4),data_source[Education Level],IF($O$4="-- Education Level --","*",$O$4),data_source[Service Years],IF($S$4="-- Service Years --","*",$S$4),data_source[Category 1 Q1.1],E$8)

on the data_source[Month] criteria section I need to do an additional check, I need to see if a date range has been selected. I will be creating a second date field in V2. So if the user selects a date in S2 only values match S2 need to be returned. If, however the user selects a date in both S2 and V2 all values within the date range needs to be returned. This is what I've come up with thus far for the [Month] criteria part of the formula:

data_source[Month],IF($V$2="-- Month --",IF($S$2="-- Month --","<>",$S$2),IF(AND(data_source[Month]>=$S$2,data_source[Month]<=$V$2),10,100))

I'm not sure what to put where the 10 is and the 100 is.

Here's the sample workbook

http://www.excelforum.com/attachment...oard_v1.0.xlsx

Thanks

OMG!!!

Considerable respect to anyone who sorts this one out without seeing the formula in context (in an Excel sheet posted on the Forum).

wow that is going to take some figuring out

I think, I would add a few (?) helper columns on your data sheet and combine records there. That way, you can probably significantly reduce all those IF's by referencing the helper, instead of all teh various cells

the normal format for countifs between dates is:

=COUNTIFS(Data range,">="&Cell containing start date,Data range,"<="&Cell containing end date)

e.g.

=COUNTIFS(A1:A100,">="&B1,A1:A100,"<="&B2)

not sure if this helps...

Have you given any consideration to adding helper columns, to simplify those monsters?

Another option might be to use Pivot Tables here?

Am I missing something - I can't see Period or Month columns in your table?