How to Count Between 1 and 16 in Google Sheets

A question from the Facebook group "I Love Google Sheets" wants to know how to count numbers between 1 and 16 in a series.  So let us break it down and look at the best way to do this.

The question was "How can I count the number of values in column B that are between 1 and 16?”

1. Using COUNT. Using the COUNT function will count the  numbers in the range.

2. Using COUNTIF. Someone suggested using COUNTIF, however, we have to add a criteria (like >0).

But what if we want to count numbers that are less than 10 or 16, and some other specific numbers like 12? COUNTIF only allows for one criterion at a time,  so we cannot specify 'less than 10' and 'less than 16' simultaneously.  Attempting this will result in a formula parse error.

If you're familiar with combining formulas, you might consider using "AND," but that approach won't work either—it'll just return 0.

3. Using FILTER. Since COUNT and COUNTIF doesn’t work, we can use FILTER as it will allow us to put multiple conditions to be applied simultaneously.

using FILTER function only will only give as #REF! or a list of answers.

So, what we'll do is apply COUNTA. By combining FILTER with COUNTA, we now have our answer: 7.

We can also do: =COUNTA(FILTER(B2:Q2, B2:Q2 <=10, B2:Q2 >0))

We wrap FILTER with COUNTALL and we get 7, which is the correct answer.

We can even include the top number which is 12, and we get 8. 

This way is more advanced than using COUNT alone, which simply counts the number of numeric values without any conditions.

Using FILTER combined with COUNTA allows for counting with multiple conditions, providing a more accurate and flexible solution. This approach will also help us  avoid common errors and limitations of basic counting functions.

More tutorials at http://bettersheets.co/tutorials