Different ways of using Single Criterion SUM function in Excel

We have already seen the basic syntax and how to use single criteria SUM in Excel. In this post, we will see how use conditional sum based on difference scenarios. For detailed explanation on SUMIF function please read here.
Examples for Using conditional sum Excel Function SUMIF: Below are the some of theExcel example based on different situations.

Sample Data

1. Summing only Negative OR Positive values using Excel SUMIF Function: 

The below formula will add the "difference" column values whichever is less than zero.
=SUMIF(E2:E20,"<0 -49="-49" nbsp="nbsp" negative="negative" of="of" p="p" returns="returns" sum="sum" value.="value." which="which">
Similarly you can add the values with positive value.
=SUMIF(E2:E20,">0")

Now if we want to find the total variations, we need to sum all negative values and positive values. Below is the formula to find total sum considering absolute values.
=ABS(SUMIF(E2:E20,"<0>0") which returns 86. Here we did calculate the sum of both negative and positive values and then added these two making negative values absolute. 

Note: The SUMIF function can use three arguments. However it is not mandatory to use third argument. If we omit the third argument, then Excel adds the values in the 1st argument when the criteria is met.

2. Summing values based on a different Range:  For this we need to use the third argument of SUMIF functions.  As we know, first argument is the range which should be matched with the criteria(argument 2) and the third argument is the range which should be added when given criteria is met. Following SUMIF formula demonstrates the same.
=SUMIF(E2:E20,">0",C2:C20) which returns 940. In this example, excel adds the "price" where the difference has positive value(Greater than Zero).
3. Summing Values based on a text comparison: Some times we need to compare the text value of a range and then perform SUM.  Following are examples for the same.

=SUMIF(A2:A20,"Apple",C2:C20) which returns 444. This formula adds the range "Price" when "Data" Column has the text "Apple".
=SUMIF(A2:A20,"<>Apple",C2:C20) which returns 997.This formula adds the range "Price" when "Data" Column does not contains text "Apple".

4. Summing Values based on a date Comparison: We can use Excel SUMIF formula to add the values based on given date criteria. Following is the example for the same.
=SUMIF(B2:B20,">="&DATE(2013,1,7),C2:C20) which returns 790.
=SUMIF(B2:B20,">="&TODAY(),C2:C20) which returns 217.

Note: As you might have observed, we have used a expression as a second argument which is a criteria. The expression used in first example is DATE and in second it is Today().DATE function returns the date and Today function returns Todays date.  Also the comparisonoperator, enclosed in a quotation mark is concatenated using & operator with the result of the DATE or TODAY() function.

Please share your thoughts or queries on these examples and also share any tips for using SUMIF function you might know in the comment section.