Excel track · Exercise 4 of 86 Paid
Add only the positives
Total the positive and negative rows separately instead of netting a column.
Intermediate
Monthly changes and weekly variances
Read-only data · screen 1 of 2 · answer in B7
In B7, add up the months that went up and leave the drops alone.
| A | B | |
|---|---|---|
| 1 | Month | Change |
| 2 | Jan | 12 |
| 3 | Feb | -8 |
| 4 | Mar | 15 |
| 5 | Apr | -3 |
| 6 | May | 20 |
| 7 | Gains |
In B7, report the size of the losses as a positive number.
| A | B | |
|---|---|---|
| 1 | Week | Variance |
| 2 | W-1 | -4 |
| 3 | W-2 | 6 |
| 4 | W-3 | -9 |
| 5 | W-4 | -2 |
| 6 | W-5 | 3 |
| 7 | Losses |
Reference answer
What it does
A plain SUM over a profit-and-loss column returns the net, which is the least informative figure in it. SUMIF splits the column by sign instead: ">0" keeps the gains and "<0" keeps the drops.
Zero belongs to neither bucket, so a flat month vanishes from both totals and the pair will not add back to the net. A net that misses the column by one month's figure is that missing zero.
The second screen puts a minus in front of the function so the losses read as a size rather than a negative. The minus does not change which rows matched, so a wrong condition still shows up as the wrong size.
Syntax
=SUMIF(range, ">0") and =-SUMIF(range, "<0")Worked example
On Monthly changes and weekly variances, =SUMIF(B2:B6,">0") returns 47.
=SUMIF(B2:B6,">0")Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The criteria range holds text that cannot be compared with a number, or a comparison lost its quotes and is read as a cell reference. |
#NAME? | The function is misspelled, or SUMIFS was used with the argument order SUMIF keeps. |
Wrong sign | Losses are negative, so a report that wants their size needs the minus in front of SUMIF. Without it the figure reads as -15 rather than 15. |
At work
A monthly variance report that shows total gains and total losses on separate lines.