Excel IF/Date functions

[Nikolatos091199]

I am trying to create a function that will calculate
information within a date range.

IE: How many new entries made between 1/1/04 - 3/31/04;
How many ____ entries made between 1/1/04 - 3/31/04 (the
blank would be filled by the column label). Does any one
know how I can do this?

 

[Bob Phillips]

Nikolatos,

Try something like

=SUMPRODUCT((A1:A100>=DATE(2004,1,1))*(A1:A100<=DATE(2004,3,31))

You could put the dates in cells and test against those cells

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Graesser]

Nikolatos091199
This is similar to needing a COOUNTIF function with two criteria. You can do this with a SUMPRODUCT. Assuming your dates are in A10 through A100, you can count the number of dates between two dates by entering the two dates in A1 and A2 and using the following formula

=SUMPRODUCT((A10:A100>=A1)*(A10:A100<=A2)

If you want to count the number of time Fred appears in column B between the above dates, then enter Fred in A3 and use

=SUMPRODUCT((A10:A100>=A1)*(A10:A100<=A2)*(B10:B100=A3)

Hopefully this gets you started

Good Luck
Mark Graesse
(e-mail address removed)


----- Nikolatos091199 wrote: ----

I am trying to create a function that will calculate
information within a date range

IE: How many new entries made between 1/1/04 - 3/31/04;
How many ____ entries made between 1/1/04 - 3/31/04 (the
blank would be filled by the column label). Does any one
know how I can do this