There are some functions in Excel where any arguments allow to specify a condition so that if it is met will perform the action. Such is the case of COUNTIF function or SUMIF function.
Such functions are useful to apply an action a cause of certain condition. For example, if I want to count all the people in the range B1: B20 that are older than 30 years can apply the following formula:
But now, what if I want to count only those who are aged above average? One option would be to get the average in a separate cell and then make the comparison with the obtained value to determine whether or not you can count every item. Rather than make a separate calculation, we can make use a special quality of the COUNTIF function argument condition.
This quality is due to the argument condition is a text string and therefore can make a chain with the result of another function. Observe carefully how to perform this concatenation in the second argument of the function:
=COUNTIF(B2:B21, ">"&AVERAGE(B2:B21)) Concatenation in Excel is done with “&” and for this example I’m using the AVERAGE function so that Excel gets the average of the range and then the concatenation to get ready the argument condition.
After making the evaluation of this expression Excel shows the correct result.
For this example I used the AVERAGE function, but you can use any function that helps form the argument condition you need. (Source: Excel Total)