Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with COUNTIF formula | Excel Discussion (Misc queries) | |||
CountIF Formula | Excel Discussion (Misc queries) | |||
help in countif formula. | Excel Discussion (Misc queries) | |||
countif formula | Excel Discussion (Misc queries) | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions |