Excel COUNTIF and AVERAGEIF worksheet functions

The worksheet functions COUNTIF and AVERAGEIF can be useful. Excel's own help is fine for basic useage, but when you want the count or average criteria to be a value contained within a cell I always finish up having to Google search how to do it.

If you have the values 1 to 10 in cells A1:A10 you can use =COUNTIF(A1:A10, "<5") to count how many of the cells have a value less than 5. You get the answer 4.

Now, if you want to use the value contained in cell B1 as your criteria you may think that you need to use the following syntax:

=COUNTIF(A1:A10, "<B1")

However, this doesn't work as Excel doesn't recognise the B1 as a cell reference. You have to use the following instead:

=COUNTIF(A1:A10, "<" & B1)

This adds the cell reference to the text "<" whilst maintaining the reference to B1.