Excel: Save headaches with the SUMIF function

A hypothetical company in my mind is having a funny name contest. Each funny name submitted for the contest is assigned a (generous) bonus amount that will be paid to the employee that submitted the name.

This spreadsheet was created to keep track of the names, originating departments, and bonus amounts:

Before the bonuses can be paid out, the controller needs to know the totals by department so the expense can be properly allocated. Instead of sorting and re-sorting the list to generate SUM formulas, you can save yourself the headache and use the SUMIF function.

=SUMIF(range,criteria,sum range)

Range: The column (or row) to which the IF statement will be applied
Criteria: The condition we are testing the range against
Sum range: The column (or row) that will get summed if the criteria are met

Here’s how to create a simple summary:

1. At the bottom of the schedule, list the four departments represented. Make sure the names match exactly. Alternatively, you can copy the entire list of departments, paste below, and go to Data ribbon –> Remove Duplicates.
   
   In column C, we will add the SUMIF formulas to provide totals.
2. In C29, type “=sumif(“, select A3 through A24 as the range (hit F4 to make the range absolute), B29 as the criteria, and C3 through C24 as the sum range (hit F4 to make the range absolute). The formula should look like this:
   
3. Hit enter and drag down the formula to the other three departments. If the range and sum range were made absolute, the total of the summary should match the total above:
   

This function has saved me a ton of time for a lot of applications, not just for hypothetical funny name contests. Please note, this function can only use one criterion, so if you need to check more than one attribute look into array functions. I will cover array functions in future tips.