Formula for counting days

[nate777]

Hi There,


1)In the below sample, im trying to count(and categorize) incidents based on number of days the incidents are open.

The categories are
count/# of Incidents open 1-30 days
# of incidents open 31-60 days
# of incidents open 61-90 days
and incidents open > 90 days

Can anyone help me please.

Incident ID. Days Open
IR-2006-045 76
IR-2006-047 76
IR-2006-016 225
IR-2006-035 133
IR-2006-036 86
IR-2006-057 34
IR-2006-058 17
IR-2006-058 30
IR-2006-022 125
IR-2006-023 125

Thanks
Nate777



[/img]

 

[Scott Huish]

Try this:

Formula in E2:
=COUNTIF(B:B,"<31")

Formula in E3:
=COUNTIF(B:B,">30")-COUNTIF(B:B,">60")

Formula in E4:
=COUNTIF(B:B,">60")-COUNTIF(B:B,">90")

Formula in E5:
=COUNTIF(B:B,">90")

Book2
	A	B	C	D	E
1	Incident ID.	Days Open
2	IR-2006-045	76		1-30	2
3	IR-2006-047	76		31-60	1
4	IR-2006-016	225		61-90	3
5	IR-2006-035	133		>90	4
6	IR-2006-036	86
7	IR-2006-057	34
8	IR-2006-058	17
9	IR-2006-058	30
10	IR-2006-022	125
11	IR-2006-023	125
Sheet1

 

[barry houdini]

Are you trying to produce 4 totals, on e for each category, in which case you just need COUNTIF......or do you want to have a formula on each row to assign each incident to a category.

If it's the latter do you want a single column with the day category or are there 4 columns, each incident to be assigned to one?

 

[nate777]

Thanks so much Hotpepper, Im going to try your solution.

Barry, I'm creating weekly, monthly and Quarterly reports.
For the weekly reports, COUNTIF suffices. For the monthly and quarterly, I have to create trend reports.One of the graphs I want to auto generate is #of incidents open 1-30, 31-60, 60-90 and > 90 intervals.

I intend to use Pivot Tables/Charts for Trending, but not sure if my source data will suffice for the analysis or I should have more data(like your option 2) before I start trending.