Use of SUMIF function in Excel-
SUMIF helps to sum up values only once it matches certain criteria. It can be used to add numbers based on criteria’s like Text, Number and Dates, also can use with logical operators (=,<>,>,< ) and wildcards (*asterisk, ? Question mark).
As you noticed in the screenshot above, we need the total amount for Nikky (Sales Person), in second row we need the total values after 31st July 2018 and in the third row we need to get the total amount of those zone’s which are having Alphabet “E” in their names.
Syntax of SUMIF-
Start typing the function with equal “=” symbol and type SUMIF then press CTRL+A to get function arguments dialogue box. see below
Range- The range of cells from which you want to apply your criteria.
Criteria- The criteria regulate which cells you want to add.
Sum_range- The cells range which you want to Add [Optional]. If you omit the sum range, Sumif provides the total of range itself.
Let’s take an example and understand how SUMIF works-
We have a Sales report and we need to calculate total sales of one sales person “NIkky”.
Syntax and arguments, we already demonstrate above.
Note:-If your “Criteria” contains text then it needs to be entered using double quotation mark (“) around it.
Wildcards with SUMIF-
In this table we need to get total amount of Zone which should start with Alphabet “E”.
There are two types of wildcards available in Excel-
- Asterisk (*) –
- Asterisk supports one or more characters. Putting asterisk (*) symbol one time after the alphabet will ensure considering any data points starts with that alphabet.
- If we put asterisk symbol (*) both sides of an alphabet e.g. (“*E*”), it will take every data point which has that alphabet anywhere in it.
- Question mark (?)- while a question mark means “any one missing character”.