When using Excel logic functions such as COUNTIF and SUMIF which involve ranges being evaluated based on criteria, you cannot select the criteria from the range of data you are evaluating. This is a very common error.
In this tutorial:
Required knowledge:
1. String criteria
the data file and follow along:
The example below is an extract of some data where the COUNTIF function is used to analyse the number of respondents living in each area. You can add the criteria value to the function literally: =COUNTIF($B2:$B13, "Crags"), but then you have to edit every row’s calculation manually after you copy it down to the remaining rows.
Notice how carefully the cells have been laid out for the calculation of the answers, specifically the labels in the range E15:E20. This allows you to use the labels in your functions & formulae and facilitates the easy and correct copying of your functions & formulae (using the Auto Fill series function). The labels and headings also make it really easy for someone to understand the data you are presenting.
2. Cell reference criteria
Rather than type the criteria in as a string, we will use a cell reference as the criteria. This allows us to use the following formula in cell F15:
=COUNTIF($B2:$B13, E15)