Exceljet

Quick, clean, and to the point

Volunteer hours requirement calculation

Excel formula: Volunteer hours requirement calculation
Generic formula 
=AND(COUNTIF(rng,">=5")=3,SUM(rng)>=15)
Summary 

This examples shows how to calculate a volunteer hour requirement. The requirement is to have 15 volunteer hours, with at least 5 in each category. The formula in F6 is:

=AND(COUNTIF(C6:E6,">=5")=3,SUM(C6:E6)>=15)
Explanation 

There are two requirements that must be satisfied, and both evaluated inside a single AND statement. The first requirement is at least 5 volunteer hours in each of the 3 categories: A, B, and C. This requirement is tested with a logical statement based on the COUNTIF function:

COUNTIF(C6:E6,">=5")=3

In the range C6:E6, we count numbers greater than or equal to 5.

The second requirement is to have at least 15 volunteer hours total. This requirement is tested with a simple logical expression based on the SUM function:

SUM(C6:E6)>=15)

If both the the logical conditions return TRUE, the AND function will return TRUE. IF either returns FALSE, AND will return false.

Author 
Dave Bruns
Related functions 
Excel AND function
Excel AND Function

The Excel AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...

Excel COUNTIF function
Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

Excel SUM function
Excel SUM Function

The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.

Related videos 

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.