When you use comparison operators within the criteria arguments of your COUNTIFS and SUMIFS formula it is important that you use the correct syntax: Excel is just a little bit fussy like that. To make sense of these formula you will need to download the accompanying file.
This tutorial assumes you have a working knowledge of the COUNTIFS function and comparison operators such as > < <> and =.
Comparison Operators and Numbers
Here are some scenarios and the correct syntax to use. I have used the COUNTIFS function but the same applies to SUMIFS in terms of how to implement the operator. Notes are given below but it’s probably worth watching the video below.
Comparison Operator and Number in a Cell
F2 contains >50
C2:C26 contains numbers
=COUNTIFS($C$2:$C$26,F2)
Comparison Operator and Number in the Formula
=COUNTIFS($C$2:$C$26,”>50″) – note the comparison operator and number are in double quotes
Comparison Operator in a Cell and Number in the Formula
E2 contains >
=COUNTIFS($C$2:$C$26,E2&50) – note the & symbol is used to concatenate or join the comparison operator with the number in the formula
Comparison Operator in a Formula and Number in a Cell
N1 contains 50
=COUNTIFS($C$2:$C$26,”>”&N1) = note the comparison operator in enclosed in double quotes and the & symbol is used to concatenate or join the comparison operator with the number.
Comparison Operator in a Cell and Number in a Cell
E2 contains >
N1 contains 50
=COUNTIFS($C$2:$C$26,E2&N1) – note the & symbol is used to concatenate or join the comparison operator with the number.
Comparison Operators and Dates
Comparison Operator and Date in a Cell
F2 contains >19/10/2014
B2:B26 contains dates
=COUNTIFS($B$2:$B$26,F2)
Comparison Operator and Date in the Formula
=COUNTIFS($B$2:$B$26,”>19/10/2014″) – note the comparison operator and date are in double quotes
Comparison Operator in a Cell and Date in the Formula
E2 contains >
=COUNTIFS($B$2:$B$26,E2&”19/10/2014″) – note the & symbol is used to concatenate or join the comparison operator with the date in the formula. The date must be expressed within double quotation marks.
Comparison Operator in a Formula and Date in a Cell
N1 contains 19/10/2014
=COUNTIFS($B$2:$B$26,”>”&N1) = note the comparison operator in enclosed in double quotes and the & symbol is used to concatenate or join the comparison operator with the date.
Comparison Operator in a Cell and Date in a Cell
E2 contains >
N1 contains 19/10/2014
=COUNTIFS($B$2:$B$26,E2&N1) – note the & symbol is used to concatenate or join the comparison operator with the number.