SUMIF(). Lesson: BASIC. =SUMIF(). When Do You Use It: To add numbers based on a single criterion/condition Example: If you have a column of data with numbers and you only want to add those that are greater than a certain number. =SUMIF(range, criteria, [ sum_range ]). Required Arguments
This is how the formula looks if you simply type in the criteria you want. Note that text criteria need to be enclosed in double quotation marks.
This is how the formula looks if your criteria is a reference to a value in a cell.
Which method is better? I prefer the cell reference method for at least two reasons. First, if labeled, you can see what the criteria is without having to go into the formula. Second, I find it easier if I have to change the criteria if I just have to type over the value in the cell reference than selecting the cell with the formula, getting it in edit mode, and then typing over in just the right spot in the formula.
This is how the formula looks if you use an expression, like >, <, =, <=, etc. Note that expression criteria need to be enclosed in double quotation marks.
This is how the formula looks if your criteria is a number. It’s a lot like the text criteria except you don’t need the double quotes.
Which method is better? I still prefer the cell reference method from the last example because you can still achieve the results of both these examples. To mimic the expression criteria, just enter >25 in the cell being referenced: =SUMIF(A2:A13,D13,B2:B13) where in D13 you’ve typed >25.
This is how the formula looks if you use a formula as a criteria. Other formulas you might find yourself using are VLOOKUP(), HLOOKUP() and INDIRECT().
I don’t come across this too often, but it is available. On a more technical note, any one of the arguments could be a formula, so long as the result of the formula returns the expected data type, such as a range for the first and third arguments.