COUNTIF headscratcher

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

worzell@hotmail.com - 17 Sep 2007 16:34 GMT

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

Reply to this Message

Don Guillett - 17 Sep 2007 16:38 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello,
>
[quoted text clipped - 25 lines]
>
> Ant

Reply to this Message

Pete_UK - 17 Sep 2007 16:41 GMT

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, worz...@hotmail.com wrote:

> Hello,
>
[quoted text clipped - 25 lines]
>
> Ant

Reply to this Message

Franz Verga - 17 Sep 2007 16:43 GMT

Nel post:1190043268.931413.94440@57g2000hsv.googlegroups.com,
worzell@hotmail.com <worzell@hotmail.com> ha scritto:

> Hello,
>
[quoted text clipped - 25 lines]
>
> 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.

Signature

(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

Reply to this Message

David Biddulph - 17 Sep 2007 16:58 GMT

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.

Signature

David Biddulph

> Hello,
>
[quoted text clipped - 25 lines]
>
> Ant

Reply to this Message

worzell@hotmail.com - 17 Sep 2007 17:01 GMT

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

Reply to this Message

Pete_UK - 18 Sep 2007 01:24 GMT

Good choice !! <vbg>

Pete

On Sep 17, 5:01 pm, worz...@hotmail.com wrote:

> Thanks to everyone who replied! That was extremely quick!
>
[quoted text clipped - 5 lines]
>
> Ant

Reply to this Message