COUNTIF headscratcher

[worzell]

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

 

[Don Guillett]

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

 

[Pete_UK]

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

 

[Franz Verga]

Nel post:[email protected],

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

 

[David Biddulph]

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.

 

[worzell]

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

 

[Pete_UK]

Good choice !! <vbg>

Pete