Prev: Error Alert for Invalid Data doesn't work for data selected in col
Next: Weeknum in 2010 incorrect
From: Drew on 25 Jan 2010 06:28
I am currently trying to write a formula that looks up a centre eg Boston and
then, from another column, how many 'Bostons' appear between two given dates.
Is this possible?
Although I am writing this formula other people will have to use it and
change the date ranges regularly so it can't be overly complicated.
--
Drew C
From: Jacob Skaria on 25 Jan 2010 06:42
With data arranged as below try the below formula. Query dates are in cells
C1 & D1

=SUMPRODUCT((A1:A7>=C1)*(A1:A7<=D1)*(B1:B7="Boston"))

Col A Col B Col C Col D
1/25/2010 Boston 1/25/2010 1/28/2010
1/26/2010 NY
1/27/2010 Boston
1/28/2010 NY
1/29/2010 Boston
1/30/2010 NY
1/31/2010 Boston



--
Jacob


"Drew" wrote:

> I am currently trying to write a formula that looks up a centre eg Boston and
> then, from another column, how many 'Bostons' appear between two given dates.
> Is this possible?
> Although I am writing this formula other people will have to use it and
> change the date ranges regularly so it can't be overly complicated.
> --
> Drew C
 | 
Pages: 1
Prev: Error Alert for Invalid Data doesn't work for data selected in col
Next: Weeknum in 2010 incorrect