Excel Formula

S

Steve

Looking for just a basic formula to count between number or dates. Ex.
count between 1/2/2007 thru 1/31/07. I couldn't find anything in help
file.They show
or < ??
Click to expand...
Any help would be appriciated

Steve
 
S

Steve Yandl

It's just a subtraction problem. "=b1-a1" for example. Then you will want
to format the cell where the formula is to show a number and the result will
be the number of days between the two dates.

Steve
 
C

CLR

Assuming your dates are in column A

=COUNTIF(A:A,"<=1/31/07")-COUNTIF(A:A,"<1/2/07")

Vaya con Dios,
Chuck, CABGx3
 
R

Ron Coderre

If you're starting with a list of dates and you want to know how many are
within the range 01/02/2007 through 01/31/2007 (inclusive),
you'll probably get SUMPRODUCT responses,
but lately I've developed a new respect for the FREQUENCY function as an
alternative.

With
A1:A100 containing dates

Try this...
B1: (the end date of the prev range....eg 01/01/2007)
C1: (the end date of the range you want....eg 01/31/2007)

This formula returns the number of cells in A1:A100 that are within the
range 01/02/2007 and 01/31/2007, inclusive.

E1: =INDEX(FREQUENCY(A1:A100,C1:D1),2)

It works this way....

A1:A100 is the list of dates

C1:D1 contains these dates
01/01/2007
01/31/2007

The FREQUENCY function returns an array of 3 values (in this case):
The number of cells that are <=01/01/2007
The number of cells that are >01/01/2007 and <=01/01/2007
The number of cells that are >01/31/2007
(you don't have to specify the last range...Excel assumes it's there and
uses it)

The INDEX function looks at array of 3 values and pulls the 2nd value

Example:
If A1:A100 only contains the list of dates from
12/15/2006 through 02/15/2007,
the FREQUENCY function returns this array: {18,30,15}
which is....
the 18 days prior to 01/02/2007,
the 30 days within 01/02/2007 thru 01/31/2007,
and the 15 days after 01/31/2007

In the example....the formula returns 30

You could also write the formula this way:
=INDEX(FREQUENCY(A1:A100,--{"01-Jan-2007","31-Jan-2007"}),2)

Does that help?

Post back with more questions.

***********
Regards,
Ron

XL2002, WinXP
 
D

David Biddulph

But be aware that the "md" will give results which may be confusing when you
go beyond a month.
 
T

T. Valko

Yet another clever idea!

Just a thought......

It may be more intuitive to use the intended date range and adjust in the
formula:

B1 = 01/02/2007
C1 = 01/31/2007

=INDEX(FREQUENCY(A1:A100,B1:C1-{1,0}),2)

We know that Countif(.....)-Countif(.....) is more efficient than the
Sumproduct version. I'm wondering if this Frequency version might be more
efficient than the Countif version?

Biff
 
R

Ron Coderre

Not sure about the "more intuititive" part...
Since FREQUENCY is already on the fringes of arcane, I thought it would be
easier to explain how the "bins" work without tweaking the inputs with an
array constant and having to explain how THAT works, too!

***********
Regards,
Ron

XL2002, WinXP
 
Top