COUNTIF headscratcher
 

Hello,

I have a column of dates like below in cells A1:A11.

10/04/2007
21/08/2006
10/04/2006
16/04/2007
28/02/2005
16/01/2006
20/05/2003
01/03/2005
28/02/2005
13/10/2003
26/06/2006

What I would like to do is put a formula in the cell below (say A12)
to look at the whole range (A1:A11) and then count the cells that have
a date between 01/04/2007 and 31/07/2007 (in the above example it
would return 2).

I'm not sure if I should be using SUMPRODUCT for this as I am not too
au fait with that and it's getting late in the day!!

Any help greatly appreciated.

Cheers,

Ant

COUNTIF headscratcher
 

try this idea where b2 and b3 have your desired parameters.
=sumproduct((a2:a22=b2)*(a2:a22<b3))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
ps.com...
Hello,

I have a column of dates like below in cells A1:A11.

10/04/2007
21/08/2006
10/04/2006
16/04/2007
28/02/2005
16/01/2006
20/05/2003
01/03/2005
28/02/2005
13/10/2003
26/06/2006

What I would like to do is put a formula in the cell below (say A12)
to look at the whole range (A1:A11) and then count the cells that have
a date between 01/04/2007 and 31/07/2007 (in the above example it
would return 2).

I'm not sure if I should be using SUMPRODUCT for this as I am not too
au fait with that and it's getting late in the day!!

Any help greatly appreciated.

Cheers,

Ant

COUNTIF headscratcher
 

Try this:

=SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12))

where you can put the start date in B12 and the finish date in C12,
rather than putting them explicitly in the formula. Format the cell
with the SP formula in as General or Number.

Hope this helps.

Pete

On Sep 17, 4:34 pm, wrote:
Hello,

I have a column of dates like below in cells A1:A11.

10/04/2007
21/08/2006
10/04/2006
16/04/2007
28/02/2005
16/01/2006
20/05/2003
01/03/2005
28/02/2005
13/10/2003
26/06/2006

What I would like to do is put a formula in the cell below (say A12)
to look at the whole range (A1:A11) and then count the cells that have
a date between 01/04/2007 and 31/07/2007 (in the above example it
would return 2).

I'm not sure if I should be using SUMPRODUCT for this as I am not too
au fait with that and it's getting late in the day!!

Any help greatly appreciated.

Cheers,

Ant

COUNTIF headscratcher
 

Nel ps.com,
ha scritto:
Hello,

I have a column of dates like below in cells A1:A11.

10/04/2007
21/08/2006
10/04/2006
16/04/2007
28/02/2005
16/01/2006
20/05/2003
01/03/2005
28/02/2005
13/10/2003
26/06/2006

What I would like to do is put a formula in the cell below (say A12)
to look at the whole range (A1:A11) and then count the cells that have
a date between 01/04/2007 and 31/07/2007 (in the above example it
would return 2).

I'm not sure if I should be using SUMPRODUCT for this as I am not too
au fait with that and it's getting late in the day!!

Any help greatly appreciated.

Cheers,

Ant

Hi Ant,

this is your formula:

SUMPRODUCT(($A$1:$A$11=D8)*($A$1:$A$11<=E8))

whee D8 is the starting date and E8 the finish date.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy

COUNTIF headscratcher
 

A number of options:
=SUMPRODUCT(--(A1:A11=DATE(2007,4,1))*--(A1:A11<DATE(2007,7,31)))
=COUNTIF(A1:A11,"="&DATE(2007,4,1))-COUNTIF(A1:A11,""&DATE(2007,7,31))
[and adjust the and = if applicable]
=COUNTIF(A1:A11,"="&--"1/4/2007")-COUNTIF(A1:A11,""&--"31/7/2007") if
you're confident in the unabiguity of the dates.
--
David Biddulph

wrote in message
ps.com...
Hello,

I have a column of dates like below in cells A1:A11.

10/04/2007
21/08/2006
10/04/2006
16/04/2007
28/02/2005
16/01/2006
20/05/2003
01/03/2005
28/02/2005
13/10/2003
26/06/2006

What I would like to do is put a formula in the cell below (say A12)
to look at the whole range (A1:A11) and then count the cells that have
a date between 01/04/2007 and 31/07/2007 (in the above example it
would return 2).

I'm not sure if I should be using SUMPRODUCT for this as I am not too
au fait with that and it's getting late in the day!!

Any help greatly appreciated.

Cheers,

Ant

COUNTIF headscratcher
 

Thanks to everyone who replied! That was extremely quick!

I have gone with the simple sumproduct
=SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12)) (for no other reason than it
was posted quickly!) and that seems to be working just fine.

Many Thanks,

Ant

COUNTIF headscratcher
 

Good choice !! <vbg

Pete

On Sep 17, 5:01 pm, wrote:
Thanks to everyone who replied! That was extremely quick!

I have gone with the simple sumproduct
=SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12)) (for no other reason than it
was posted quickly!) and that seems to be working just fine.

Many Thanks,

Ant