Formula Help (countif, I think....)
 

I know for most of you this will probably be a "simple" question, but here
goes.....

I need to count the number of installs within a specific contract year.
My contract begins on 5/25, and ends on 5/24, the following year. Thus:
Year 1 = 5/25/00-5/24/01
Year 2=5/25/01-5/24/02
and so on.
I have named my column of install date data as "Delivery_Date"

Below my table, I want to summarize installs per contract year.
Right now, I have three columns as below
G=contract year #
H=begin date
I=end date
and I want to show the number of installs for each year from "Delivery_Date"
data.
Is there an easy way to accomplish this?

All help is appreciated, as am momentarily stumped.

TIA,
Sandi

Formula Help (countif, I think....)
 

Hi, Sandi-
I mocked up some sample dates in a3:a11028, and entered 5/25/2008 in
cell g3. In cell h3 I used a formula to find the end date:
=EDATE(G3,12)-1 (Note: if this formula returns an error, on the menu
click Tools Add-ins, then check the box next to Analysis Toolpak and
click OK.) In cell g4 I used a formula to create the next year's start
date: =H1+1 and I copied the formula from h3 into h4. You can copy
the range g4:h4 and paste it down for as many rows as you need.

In cell i3 I developed a count of install dates with the formula
=SUMPRODUCT(--($A$3:$A$1028=G3),--($A$3:$A$1028<=H3))

Here's an explanation of the double unary operator:
http://groups.google.com/group/micro...145474b68429fc

Dave O
Eschew obfuscation

Formula Help (countif, I think....)
 

A thousand thank yous!!!! You have no idea how much time this will save me.

Have a great weekend!
Sandi

"Dave O" wrote:

Hi, Sandi-
I mocked up some sample dates in a3:a11028, and entered 5/25/2008 in
cell g3. In cell h3 I used a formula to find the end date:
=EDATE(G3,12)-1 (Note: if this formula returns an error, on the menu
click Tools Add-ins, then check the box next to Analysis Toolpak and
click OK.) In cell g4 I used a formula to create the next year's start
date: =H1+1 and I copied the formula from h3 into h4. You can copy
the range g4:h4 and paste it down for as many rows as you need.

In cell i3 I developed a count of install dates with the formula
=SUMPRODUCT(--($A$3:$A$1028=G3),--($A$3:$A$1028<=H3))

Here's an explanation of the double unary operator:
http://groups.google.com/group/micro...145474b68429fc

Dave O
Eschew obfuscation