Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list of Occurrences
I have a list of outages at our cell sites. It is an Excel Spreadsheet with 1
row of dat per outage. The spreadsheet has 1 month of data and could be over 200 lines. I'd like to create a formula that will tell me how many outages were at any given site. Now, we have over 300 sites and not everyone of them has an outage. Is there a formula that will review the data and return something like: Casco 3 Brussels 2 Abrams 4 Where the number would be how many times an outage occured at that site. I only want to see sites with more than 1 outage in a month. Any help would be appreciated (I've been pouring over help sites for hours!) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list of Occurrences
A Pivot table could do this. Or, suppose you have a list of sites in
A1:A300. Then, you could use =COUNTIF(DataSheet!C$1:C$200,A1) and fill down from A1 through A300. In this formula, it assume that: 1) DataSheet is the name of the worksheet that lists the outages, and 2) that the sites on DataSheet are listed in cells C1:C200. COUNTIF returns the number of items in DataSheet!C1:C200 whose value is equal to A1. When you fill the formula down the column, Excel will change the A1 to A2 then A3 and so on down the range. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 14 Oct 2008 14:24:01 -0700, janut24 wrote: I have a list of outages at our cell sites. It is an Excel Spreadsheet with 1 row of dat per outage. The spreadsheet has 1 month of data and could be over 200 lines. I'd like to create a formula that will tell me how many outages were at any given site. Now, we have over 300 sites and not everyone of them has an outage. Is there a formula that will review the data and return something like: Casco 3 Brussels 2 Abrams 4 Where the number would be how many times an outage occured at that site. I only want to see sites with more than 1 outage in a month. Any help would be appreciated (I've been pouring over help sites for hours!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return multiple occurrences of a match in an array? | Excel Worksheet Functions | |||
Extracting List of Occurrences | Excel Worksheet Functions | |||
How to determine the top 5 occurrences from a list of numbers? | Excel Discussion (Misc queries) | |||
Return a list dependent upon the selection of a preceeding list | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) |