Create Efficient Formulas with @cell

When you want to perform calculations in formulas that look at ranges of cells, for example: SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the @cell argument in the criteria of the function. The @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range, making your formula more efficient.

For example, using the sample data in the image below, if you wanted to sum the cost of all tasks whose due date occurred in 2014, you would create the following formula:

=SUMIF([Due Date]1:[Due Date]5, YEAR(@cell) = 2014, Cost1:Cost5)

@cell formula

The result of this formula is $875.75 (the sum of the cost for all tasks whose due date occurred in 2014).

TIP: Add the IFERROR function to your function to ensure that you don't get an error if @cell encounters a blank cell in the formula. For example: =COUNTIF(Date:Date, IFERROR(YEAR(@cell), 0) = 2016)