Microsoft Office Tutorials and References
In Depth Information
Looking for a Sign
Figure 7-8:
Using SIGN
to help
correctly
sum
amounts.
As shown in Figure 7-8, values in Column B equal 1 when the associated value
in Column A is positive. Column B displays –1 when the associated value is
negative. This information is then used in a SUMIF function, which selectively
sums information from Column A.
In cell B18 is this formula: =SUMIF(B3:B15,1,A3:A15).
In cell B19 is this formula: =ABS(SUMIF(B3:B15,-1,A3:A15)).
The SUMIF function is used to indicate a criterion to use in determining
which values to sum. For the sum of sales in cell B18, the presence of the
value 1 in Column B determines which values to sum in Column A. For the
sum of returns in cell B19, the presence of the value –1 in Column B
determines which values to sum in Column A.
Also, the ABSOLUTE function (ABS) is used to present the number in cell
B19 as a positive number. The answer in cell B19 is the sum of merchandise
returns. You would say there was \$64.18 (not –\$64.18) in returned