The SUMIF Function
The SUMIF function needs 3 parameters.
- The range of criteria
- The criteria
- The range to add
For instance, you want to count the number of pen sell in the month.
In H4, write the beginning of the formula and select the range of cell where you have your product.
Then, you write your criteria or the reference where you have your criteria. At this point, there is no difference with the COUNTIF function.
You finish with the range of cell where you have your data to add. So, if you want to return the number of pen sold, you will select the range C3:C12.
The result is 245 ; 75 (row 3) + 50 (row 6) + 100 (row 8 ) + 20 (row 12)
If you want to return the amount of sales for the pen, you select here, in this case the column E as 3rd parameter ; the result is 367,5.
Fill the following document with the function SUMIF. In the row 4, you have an example (double-click on cells to edit it).
Example with Greater Than
Now, as the COUNTIF function, you can have, as a criteria, a condition (greater than or less than).
For instance, you want to know the amount of sales before or after the 10/08/2011. For the first criteria, you will select the range of cell with the dates because our criteria is a date.
The second criteria can be written in two ways
- first: “> = 10/08/2011”
- or if you want to use the value in cell “>” & G4
The symbol & is compulsory to link the operator, between quotes, and the cell reference.
For the last parameter, you select the range of cell ‘Totals’ because that’s what you want to add.
Your formula is:
In the following workbook, you have an example with the operator “less than”