Formula for counting days

nate777

New Member
Joined
Aug 2, 2006
Messages
27
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]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDE
1Incident ID.Days Open
2IR-2006-045761-302
3IR-2006-0477631-601
4IR-2006-01622561-903
5IR-2006-035133>904
6IR-2006-03686
7IR-2006-05734
8IR-2006-05817
9IR-2006-05830
10IR-2006-022125
11IR-2006-023125
Sheet1
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Aug 2, 2006
Messages
27
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,303
Members
419,560
Latest member
g3org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top