count between dates

[Vicki Leibowitz]

I have dates in column A and names in column B. I need to count how many
names there are say between 1 Jan & 8 Jan. Help before my brain explodes....

 

[Bob Bridges]

Here's how I'd do it, Vicki: Create a helper column, say in N,
"=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE)
and it'll count how many rows have dates in that range.

Do you need to eliminate duplicate names?

 

[Vicki Leibowitz]

Hi Bob.
Basically I have named the date range as "Date" and the name range as
"Names". I have used another sheet for my formulas and have 2010/01/01 in
cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
I tried your formula but it comes back as FALSE?
Surely if I can sumproduct between dates there must be a way to
countproduct? I don't need to eliminate any duplicates.
I am a new excel learner.
Your assistance is much appreciated.
Thanks,
Vicki

 

[Mike H]

Hi,

=SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))

Where J1 and k1 are you first and last dates
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Vicki Leibowitz]

Hi,
I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
gives me #NA.
Am I just being a complete idiot? I'm sure feeling like one.

 

[David Biddulph]

That suggests that you've got the #N/A error in at least one of the cells
which are feeding into the formula.

Mike did, of course, mean "Where J7 and K7 are you first and last dates",
rather than J1 and K1 (or he meant to change the J7 and K7 references in the
formula to J1 and K1), but that would not give an #N/A error so you need to
look at your input data values.

 

[Vixter]

Hi all,
Thanks for assistance. I started from scratch and managed to get the answers
using the sumproduct formula:
=SUMPRODUCT((Date>=B2)*(Date<=B3))
So basic I could kick myself!
Thanks again.

 

[Vixter]

I gathered thanks.
I figured it out - the formula turned out to be so basic!
I should start learning what the different error messages mean.
Thank you.

 

[Mike H]

Thanks for spotting my error David.

Vixter,

The formula =SUMPRODUCT((Date>=B2)*(Date<=B3))
will corectly count dates in a range but based upon the original post it
doesn't answer the question.

I have dates in column A and names in column B.

What if column B has blanks in?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.