Count and summarise records in Excel 2003 [Solved/Closed]

Report
-
 GRA -
Hello,
Sorry to bother you but I have a minor problem with a spreadsheet and I don't know how to resolve the issue or whether it is possible using standard functionality in the product. All I need to know is - what is the function or should I be using a different approach to calculate and produce the summary table as shown below.


What I'm looking for is this = Description Number of Records

Start Provision 7
Leave Provision 7
Short Outcome 11
Sustained Outcome 5

I've tried using the COUNTA function which tells me the number of lines in the worksheet which have nonblanks but I wanted to summarise these by stating the number of records that match a specific Description e.g. 'Start Provision' and count the number of records that have that description.

Is this possible please?....Any guidance appreciated......Thanks...GRA


Input Data

Start Provision
Start Provision
Leave Provision
Short Outcome
Short Outcome
Short Outcome
Short Outcome
Start Provision
Leave Provision
Start Provision
Leave Provision
Short Outcome
Sustained Outcome
Short Outcome
Short Outcome
Start Provision
Leave Provision
Short Outcome
Sustained Outcome
Start Provision
Leave Provision
Short Outcome
Sustained Outcome
Start Provision
Leave Provision
Short Outcome
Sustained Outcome
Start Provision
Leave Provision
Short Outcome
Sustained Outcome

2 replies

You could use the COUNTIF function.
Let's assume your input data was in the range A1:A31, you can use =COUNTIF(A1:A31,"Start Provision")

Fiirst you specify the range. Then, you specify the criteria you want to count.
Use that formula for each item you want to count.


=countif(A1:A31,"Start Provision")
=countif(A1:A31,"Leave Provision")
=countif(A1:A31,"Short Outcome")
=countif(A1:A31,"Sustained Outcome")
Many thanks, this is exactly what I wanted to achieve. Much appreciated. Regards, GRa