A dynamic SUMIF() function based on naturally occurring data

It's tempting, but inefficient, to use literal values as the criteria in Excel's SUMIF() function. Reference the criteria in data instead, when possible.

Excel's SUMIF() function adds values that satisfy specific criteria. For instance, you might use this function to sum all the products sold by specific personnel. The sheet below shows this function returning the total of products sold by Bill, 120. The formula in E3


sums the sold values in column B when the corresponding cell in column A equals the string "Bill."

It's likely that you're familiar with this function and have even used it. The only drawback to using this function as I just did is the second argument, the criteria. As written, the criterion is static—the function will always look for the string Bill. If you only have a few criteria items to match, that's not such a big deal. On the other hand, entering a large number of functions would be a tedious and error-prone task.

Whether you need just a few or several functions, there might be an easier way—the criteria is probably in your data! By referencing the data, you can make a dynamic function  and copy it as you would a regular SUM() function. In this case, the solution is to use the criteria as sheet labels.

Instead of "hard-coding" the criteria, refer to the new label cell in column D2, which contains the string Bill. The function now refers to cell D2 instead of a literal string


The final step is to copy the formula to copy the function. Creating dynamic formulas and function is basic to Excel, but you might not consider using the naturally occurring data as I've done in the SUMIF() function.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks