How to use ‘greater than’/’less than’ with SUMIF and COUNTIF

Do you want to sum all numbers above/below a certain value with SUMIF? Or maybe count them with COUNTIF?

This guide is for you who are not quite sure about the criteria operators within SUMIF and COUNTIF.

If you have not already watched our lesson about SUMIF and COUNTIF, do that before you continue.

Criteria Operators

An IF function can look like this:

=IF(A1>10, A1, 10)

This function returns the value of A1 if the value in A1 is greater than 10.

But using criteria operators like the > symbol is not as straightforward in the SUMIF or COUNTIF functions.

To use operators in SUMIF/COUNTIF, you must wrap the criteria with double quotes.

In the example in the screenshot, “>100” is the correct way of writing the criteria.

To sum all the numbers greater than or equal to 100, you need to use >= as the criteria operator.

In that case, the correct way of writing the criteria is “>=100”.

The same principle behind how you write the criteria applies to these functions:

• SUMIFS
• COUNTIF
• COUNTIFS
• MAXIFS
• MINIFS