Countif criteria date range in same column

[sam99]

I want to use countif function to count the number of dates that fall in the given range.

24-Jul-12
19-May-10
19-May-10
8-Aug-12
15-Jan-11
15-Jan-11
5-Jul-12
18-Nov-10
2-Dec-10
16-Jul-12
25-Jul-12
16-Jul-12
19-May-10
12-Jul-12
12-Jul-12
2-Aug-10
5-Jul-12
18-Nov-10
2-Dec-10
16-Jul-12
25-Jul-12
16-Jul-12
19-May-10
12-Jul-12
12-Jul-12
2-Aug-10
2-Aug-10
7-Feb-12
19-May-10
19-May-10
26-Jan-12
1-Jul-10
4-Sep-12
19-May-10
25-Jun-12
19-May-10
28-Jun-12
1-Jul-10
15-Jan-11

=sum(COUNTIF(C:C,"<"&DATEVALUE("8/1/2012"),">"datevalue("8/1/10")

the same format works when I use it with multiple text criteria ( which i discovered in this forum :-)), howver for some reason it gives an error with the date function.

I would not prefer to use the suproduct function as it is a volatile function.

The only option i can think of is to use the same range twice.
=SUM(COUNTIFS(C:C,"<"&DATEVALUE("8/1/2012"),C:C,">"&DATEVALUE("8/1/10")))

Any thoughts / suggestions on why the function will not work with 2 date criteria. Appreciate the help.

thanks

[oeldere]

See the file.

[sam99]

Thanks Oeldere. However, i was hoping to refer to the column only once.

[oeldere]

I added the cells B1 and C1 to make the solution more flexibel.

What do you mean with refer to the column only once?

[sam99]

=sum(COUNTIFS($A$1:$A$39,(">="&$B$1,"<="&$C$1)))
here's how i want the formula to work but it does not

[oeldere]

1) And what is the answer of your formula?

2) Why do you want to sum the value?

[sam99]

Sorry for responding so late.
I do not get an answer to the formula that I typed and I would like to find out the reason. The function works if I had 2 text criteria instead of a date criteria.
normally the sum function is used with the countif function when you have multiple criteria for the same column as is in this case.

thanks

[ChemistB]

I don't think it can be done. Why don't you name the range something short (drng or something). That would make things easier.

[icestationzbra]

with text criteria, you are not using ">" or "<" operators; you are just using implicit "=" operator within an "array constant", with the following construct:

=sum(countif(a:a,{"first_criterion","second_criterion"}))

the operating phrase here is "array constant", which does not allow for any ">" / "<" / "=" operator to be included within the flower / curly brackets.

you cannot extrapolate constructs for alphabetical operations onto those requiring numerical calculations directly all the time (or vice versa); this is one of those cases.

[sam99]

Thanks. That makes lot more sense.
I learnt something new about the array constant. I will be careful about extending the logic from alphabetical operation to a numeric one or vice versa the next time.
Is there a way in excel that one could possibly write multiple criteria {< datevalue("12/10/12"); > datevlaue("11/12/12")}referencing the same column, in instances like these if one has to count the date values falling in a given range.

Thanks

[icestationzbra]

by the way, SUMPRODUCT is NOT a volatile function; it is an implicit "array" function that is slow because of that reason, that's all.

[sam99]

Ok.. thanks for the input. I had read on chandoos forum that sumproduct is a volatile function. since the function is so slow, i assumed that to be true. Thanks for clearing that up.

[icestationzbra]

well, for the example you quoted, previous suggestions of SUMPRODUCT or COUNTIFS would be the way to go; you would need to reference the column in question multiple times, and you cannot use the "array" braces.