Essential Excel Formulas

Estimated time: 30 minutes

This lesson introduces formulas that help you calculate results automatically. A formula in Excel normally begins with an equals sign: =

Assume the patient counts below are in cells B2 through B5.

Facility	Patients
Central Clinic	120
Riverside Clinic	95
Hilltop Clinic	110
Valley Clinic	75

Formula 1: SUM

SUM adds numerical values. Enter =SUM(B2:B5). The result is 120 + 95 + 110 + 75 = 400. The total recorded attendance is 400.

Formula 2: AVERAGE

AVERAGE calculates the arithmetic mean. Enter =AVERAGE(B2:B5). The result is 400 ÷ 4 = 100. The average recorded attendance per facility is 100.

Remember that this is an average of the four supplied values. It is not automatically a measure of service quality or population coverage.

Formula 3: COUNT

COUNT counts cells containing numbers. Enter =COUNT(B2:B5). The result is 4. This tells you how many numerical entries are present in a range.

Formula 4: COUNTA

COUNTA counts non-empty cells. Enter =COUNTA(A2:A5). The result is 4, because four facility-name cells contain entries. COUNTA does not determine whether the entries are valid or correct.

Formula 5: COUNTIF

COUNTIF counts cells that meet a specified condition. To count facilities recording more than 100 patients, enter =COUNTIF(B2:B5,">100"). The result is 2, because Central Clinic recorded 120 and Hilltop Clinic recorded 110.

Formula 6: SUMIF

SUMIF adds values that meet a specified condition. To add the attendance for Central Clinic, enter =SUMIF(A2:A5,"Central Clinic",B2:B5). The result is 120. SUMIF is especially useful when you need totals for selected categories.

Formula 7: IF

IF returns one result when a condition is true and another when it is false. If a facility has a target of 100 patients and its attendance is in B2, enter =IF(B2>=100,"Target Met","Below Target").

If B2 contains 120, Excel returns Target Met. If B2 contains 95, Excel returns Below Target. This example assumes that the target of 100 is meaningful for the intended measure. In real health reporting, the target and the measure must be clearly defined.