Jennifer,

You have a case of what I like to call multiconditional count. COUNTIF is used in case you have a single condition.

Supposing that you have the date entries in A2:A600.

Put 1/01/01 in say B1 and 1/31/01 in B2.

In B3 enter: =SUMPRODUCT((A2:A600>=B1)*(A2:A600<=B2))

I interpreted "between" as "Inclusive between".

Equivalently, you can use the following formula, if you so desire:

=SUM((A2:A600>=B1)*(A2:A600<=B2)),

which must be entered by hitting CONTROL+SHIFT+ENTER at the same time (not just ENTER).

Aladin

=============

I got the second formula to work fine. The first one kept returning zeroes. Oh well, I got the answer I needed.

Now, for a followup question that's probably been answered a jillion times. What's up with the Ctrl+Shift+Enter? Why not just plain Enter?

The first one should work too. Even unchanged. Try however the following:

=SUMPRODUCT(((A2:A600>=B1)*(A2:A600<B2))+0)

Oh well, I got the answer I needed.

The second formula is an array formula. Control+shift+enter tells Excel that the formula must be treated as such.

Activate the cell where you placed this formula, go to the Formula Bar, select a term (an subexpression within formula, e.g., (A2:A600=B1) and hit F9. Do this for each term, you'll probably see how such a formula works: by creating arrays of values. When ready, hit ESCAPE. Otherwise you'll loose the formula.

Aladin

PS. Make that SUMPRODUCT formula work.

================================

You can use the above proc with SUMPRODUCT and other formulas.

===========

Heh, got the SUMPRODUCT one to work too! Thanks Aladin - you're a GENIUS... or is that =GENIUS(A1:A100)?

jen

I just found another way of doing it...

=FREQUENCY(A2:A2000,J2:J30)

Where A2:A2000 is my data, and J2:J30 is the upper range of the dates that i wanted to count (i.e., 12/31/74, 12/31/75, etc).

And it's an array formula too! Uhoh, I'm turning into an excel geek!

Is there any advantage/drawback of doing it one way vs the other? SUM, SUMPRODUCT, and FREQUENCY all seem to work fine.