Sales: 0208 732 5656

Excel top tip: SUMIF and COUNTIF

Excel top tip: SUMIF and COUNTIF

These two surprisingly uncommon functions can do some very clever things for you on chunks of data. Individually SumIF can search for specific content in a range and then add it to a corresponding amount in another range. So you can total all transactions for a specific product for the year for example.

=SUMIF(A2:A20, “Apples”, E2:E20)

CountIF will count the number of a recurrences of a specific content in a range of data. So you can count the number of times “Apples” is listed in your data for example.

=COUNTIF(C2:C11, “Apples”)

You can use these functions together to calculate averages.

=SUMIF(A2:A20, “Apples”, E2:E20) / COUNTIF(C2:C11, “Apples”)

And by substituting the word apples by a cell reference you can have the formula running on an entire column.

No Comments

Sorry, the comment form is closed at this time.