1 

•

•

Quick Reference Guide to Crisis Counseling Assistance and Training Program (CCP) Data Analysis (Excel) 

This guide is intended to help you generate certain statistics from your CCP project data and to report project activities. The following 

step-by-step procedure will show you how to download comma-separated values (CSV) data from the Online CCP Data Collection 

and Evaluation Site and calculate totals for the following variables. 

1. Individual/Family Encounters

o

o

o

Total numbers of individuals participating in individual/family encounters

Total numbers in each age group for individual/family encounters

Overall focus of individual/family encounter

2. Group Encounters

o

o

o

Total number of individuals participating in group encounters

Numbers of people in public education sessions versus in group counseling

Overall focus of group encounter

3. Weekly Tally

o Brief in-person

o

o

o

o

Telephone

Hotline

E-mail

Community networking/coalition building

o

o

o

Materials handed to people

Materials mailed to people’s homes and left in public places 

Mass media and social networking

Number of contacts made

Numbers of materials distributed 
2 

1. Individual/Family Encounters

Step 1: Download the Individual/Family Encounter Log data from the CCP online database by clicking on Download CSV,

and save the file as an Excel workbook. 
3 

Step 2: Sort and filter: Depending on the amount of data you have, you may want to sort to make the data easier to read or 

understand, and you may also want to filter the data by the variables of interest (e.g., state, provider, date of service, etc.). 

To sort a column, select all cells and select Sort from the Data Menu. 

Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and 

ultimately make more effective decisions.

To filter a column, select all cells and select Filter from the Data Menu. 

Filtering data is a quick and easy way to find and work with a subset of data in a range of cells or table. For example, 

you can filter to see only the values that you specify (e.g., you can filter by project number, by provider, by date of 

service, etc.). After you have filtered data in a range of cells or table, you can either reapply a filter to get up-to-date 

results, or clear a filter to redisplay all of the data.

Filter

Sort
4 

Tip: You can select all cells by clicking on the box in the upper left-hand corner of the worksheet (above Row 1 and to the left 

of Column A), or by pressing CTRL + A or using Select All from the Edit Menu.

NOTE: It is important to select all cells before sorting. Only the selected cells are sorted, and if some rows are sorted 

and others are not, the worksheet may be inaccurate. 

Step 3: Locate the column that contains the variable of interest. 

Step 4: Enter a formula in a cell. 

To enter a formula, do the following: 

A. Select the cell that will contain the formula. 

B. Type = (an equal sign). 

C. Type the formula. You may type directly in the cell or in the formula bar at the top of the worksheet (the 

formula will appear in both places). 

 Note: The letters and numbers indicated in the formulas provided throughout this document should be adjusted 

based on the data range (columns and rows) being analyzed.

For example: =SUM(A1:AX)

A—Stands for the letter that identifies the column that contains the variable of interest (the variable being analyzed) 

1—Stands for the number that identifies the row that contains the first record of the variable being analyzed 

X—Stands for the number that identifies the row that contains the last record of the variable being analyzed
5 

1.1 Formula to calculate the total number of individuals participating in individual/family encounters

=SUM(K1:KX)

K—Visit type code column

1—The first record in the data range

X—The last record in the data range
6 

1.2 Formula to calculate the total number of individuals in each age group

=SUM(N1:NX) 

N—Male preschool column

1—The first record in the data range

X—The last record in the data range

To calculate totals for the remaining age groups, follow these steps:

•

•

•

Select the cell with the formula. 

Point to the fill handle, at the bottom right of the selected 

cell. 

When the pointer changes to a black plus sign, press and 

drag outside selection to extend series.
7 

1.3 Formula to calculate the focus of encounter

=SUM(CH1:CHX) 

CH—Information about_ 1 column

1—The first record in the data range

X—The last record in the data range

•

•

•

Follow these steps to calculate totals for the remaining columns (focus of 

encounter):

Select the cell with the formula. 

Point to the fill handle, at the bottom right of the selected cell. 

When the pointer changes to a black plus sign, press and drag 

outside selection to extend series.
8 

2. Group Encounter

 Step 1: Download the Group Encounter Log data from the CCP online database by clicking on Download CSV, and save the 

file as an Excel workbook. 

Then repeat steps 2–4 above (on pages 2–4). 

2.1 Formula to calculate the total number of group encounters

=SUM(R1:RX) R—Participant total column

1—The first record in the data range

X—The last record in the data range
9 

2.2 Number of public education sessions versus group counseling

2.2.1 Formula to calculate the frequency of public education sessions 

=COUNTIF(K1:KX,1)

K—Service type code column

1—The first record in the data range

X—The last record in the data range

1—Represents public education session
10

2.2.2 Formula to calculate the frequency of group counseling

=COUNTIF(K1:KX,2) K—Service type code column

1—The first record in the data range

X—The last record in the data range

2—Represents group counseling
11

2.2.3 Overall focus of encounter

To calculate the overall focus of encounter, you will need data from column AE through column AN. 

=SUM(AE1:AEX)

AE—Information about 1 column

1—The first record in the data range

X—The last record in the data range

To copy this formula to additional columns in the row (columns AF through AN) drag the fill handle outside selection. Release the mouse button, 

and the selected area will be filled with the corresponding total in each columns. 
12

3. Materials distributed and number of contacts

Information about brief educational supportive services is collected on a Weekly Tally Sheet. To calculate the total number of 

materials distributed by each category—materials handed to people, materials mailed to people’s homes and/or left at a person’s 

unattended home, materials left in public places, mass media, and social networking messages—you will need to download the 

Weekly Tally Sheet from the CCP online database. 

Step 1: Download the Weekly Tally Sheet from the CCP online database by clicking on Download CSV, and save the file as an Excel 

workbook. 

Then repeat steps 2–4 above (on pages 2-4).

3.1 Formula to calculate the total number of materials distributed

=SUM(BE1:BEX) BE—Total material handed to people

column

1—The first record in the data range

X—The last record in the data range
13

To calculate totals for the remaining variables (materials mailed to people’s homes and/or left at a person’s unattended home, 

materials left in public places, mass media, and social networking messages), you would repeat the same steps above but change the 

columns and data ranges. Examples are provided in the following table: 

Variables Column Formula Note

Materials mailed to people’s 

homes and/or left at a person’s 

unattended home 

BM/Total 22 =SUM(BM1:BMX)

1 and X should be adjusted to reflect 

Materials left in public places BU/Total 23 =SUM(BU1:BUX) the data range under analysis. 

Mass media CC/Total 24 =SUM(CC1:CCX)

Social networking messages CK/Total 25 =SUM(CK1:CKX)
14

3.2 Formula to calculate total number of contacts with a breakdown by each mode of contact 

=SUM(Q1:QX) Q—Total material handed to people column

1—The first record in the data range

X—The last record in the data range

To calculate totals for the remaining variables (telephone contacts, hotline, e-mail, and community networking/coalition building) you 

would repeat the same steps above but change the columns and data ranges. Examples are provided in the table below.

Weekly total for: Column Formula Note

Telephone contacts Y/Total 12 =SUM(Y1:YX) 1 and X should be adjusted to 

reflect the data range under 

analysis.

Hotline AG/Total 13 =SUM(AG1:AGX)

E-mail AO/Total 14 =SUM(AO1:AOX)

Community networking/

coalition building

AW/Total 15 =SUM(AW1:AWX)