Using date fields as reference with COUNTIFS?

[dubcap01]

Hi all,

Attached is example workbook with two instances of what I thought might yield the same result.
In the tab SUMDATA, I am trying to use the dates in rows 2 and 3 as the reference for a COUNTIFS.

For the column with the dates for April 2015 ( the result in D4), the formula works fine with this reference:

=COUNTIFS(COHORT!$C:$C,">=1/4/15",COHORT!$C:$C,"<=30/4/15")

but that means I have to reference the start and end dates manually. I'd like a solution like the one in the column for March 2015 (the result in cell C4), for which this code doesn't work:

=COUNTIFS(COHORT!$C:$C,"C2",COHORT!$C:$C,"C3")

Does anyone know a way to reference cells C2 and C3 to be part of the criteria for COUNTIFS?

Hope that makes sense!

cheers
Pete

[Sam Capricci]

I'm having trouble finding the formulas you referenced above in your attached sheet.
but in general if you change this...=COUNTIFS(COHORT!$C:$C,">=SUMDATA!D2",COHORT!$C:$C,"<=SUMDATA!D3")
to this... =COUNTIFS(COHORT!$C:$C,">="&SUMDATA!D2,COHORT!$C:$C,"<="&SUMDATA!D3)
it will work.

[Pete_UK]

Remove the quotes from around C2 and C3 in your second formula. If you put them in the function will take the literal string "C2", instead of the contents of C2. You also need comparison operators, so the formula should probably be something like:

=COUNTIFS(COHORT!$C:$C,">="&C2,COHORT!$C:$C,"<"&C3)

Hope this helps.

Pete

[dubcap01]

Thanks Pete,

Your suggestion worked great! Problem solved!

cheers
Pete