Technology Training Community

The other day, someone asked me about reducing the size of a formula that had grown quite long. Basically, the formula looked at one column to determine if a statement was true, then took a value from a different column in the same row (think database record) and added it to other like rows. Part of the problem was that the formula was looking for each of two different values to determine if the formula was indeed true.

Using the COUNTIF function, it may have looked something like this:

=COUNTIF($I$18:$I$476,"Attend")+COUNTIF($I$18:$I$476,"Attend - Deferral")

By using the wildcard in the function to account for the two different values ("Attend" and "Attend - Deferral"), we were able to shorten the formula to

=COUNTIF($I$18:$I$476,"Att*")

and achieve the same result.

Another example was

=(AVERAGEIFS($K$18:$K$476,$I$18:$I$476,"Attend")*(COUNTIF($I$18:$I$476,"Attend")/$B$9))+(AVERAGEIFS($K$18:$K$476,$I$18:$I$476,"Attend - Deferral")*((COUNTIF($I$18:$I$476,"Attend - Deferral")/$B$9)))

which was simplified to

=(AVERAGEIFS($K$18:$K$476,$I$18:$I$476,"Att*"))

So, if you are working with large formulas or functions, see if the use of a wildcard can help in reducing the size and complexity of the formula/function.