=Sumif(Range, Criteria, SumRange)
When creating Excel schedules at work, I often see a series of cells that need to be added together, but with cells in between them that should not be part of this total. Most of the time, there are only two or three cells that need to be totaled - in this case, it is often quicker to just do a basic formula, +D10+H10+L10. This is easy to do, and everyone in the office knows how to set this up. This comes up a lot because when a loss extends multiple months, we often have a Projected, Actual, and Loss column for each month, with the totals in the last columns. However, if the loss extends for over a year, that is a lot of cells to go through and click on.
The key to the Sumif formula is the column heading (at least in this example). The arguments for Sumif are range, criteria, and sumrange. In this case, it's a bit counterintuitive. The Range argument refers to where the criteria will relate to. In this case, it's not the numbers that are selected, but the column headings that say Projected, Actual, and Loss. The cell references in this case should be absolute ($D$9:$N$9). The Criteria argument will point to the column heading in the current column, row absolute (P$9), and the sumrange will point to the actual numbers you're adding, column absolute ($D10:$N10). Each cell in the Range argument corresponds to a cell in the SumRange argument. Therefore, the references used should include the same number of cells, and be contained in the same columns, if possible. This formula can then be copied to each column and row in the total columns.
Still with me? I hope so.
Sumif can also be used if you would only like to add up the negative numbers in a column, or the numbers greater than 1,000, or anything like that. In this case, the Range argument points to the numbers themselves, and the SumRange argument can be left off completely, as the SumRange argument is optional and should only be used when the cells to be added are different.
To write the Criteria portion, put the Criteria in quotes. "<0" or ">1000". But, you can only have one criteria. Numbers by themselves can be left out of quotes.
Now you can use the Sumif formula to avoid all that pesky clicking on lots of blocks, and easily picking out certain values to add.
Questions or comments, feel free.