# Formula for counting days

#### nate777

##### New Member
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

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
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

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?

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.

Replies
2
Views
465
Replies
3
Views
138
Replies
9
Views
212
Replies
3
Views
141
Replies
4
Views
389

1,219,899
Messages
6,150,842
Members
450,987
Latest member
PopeScooby

### 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.

### Which adblocker are you using?

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

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