I came across this while working with a spreadsheet that was perhaps a little too fancy to come over from Excel to OpenOffice Calc.
Let's say you keep a spreadsheet full of various numbers, and they fall into about five or six categories. You have a bunch of things that are categorizable as:
business expense; donations; mortgage interest
toys; paints and crayons; DVDs; nap supplies
music festivals; improv classes; book club purchases; dance lessons
or other categories.
You want to add up all the items that are in one particular category since you want to know how much you're spending on music festivals; how much on climbing related purchases; etc. But the items are entered like this. Not really well categorized.
So here's what you do.
You enter all your information however you want, just make sure you have a column, as shown above, to indicate the category.
Then you have a separate summary section (or placed wherever you want) for the totals. This one shows the totals already there.
How do you get the total amount you're spending on dance in January, and on climbing in April, in there?
You use SUMIF()
Here's the syntax.
SUMIF(range containing all the categories where you entered the detail; the category you want to add up that's in the summary area such as the cell for March climbing expenses;the column of numbers you want to add up such as the detail section's entire March column)
Here's an example in the spreadsheet, with the colored squares showing the cells or cell ranges being referenced. Note that the month is March for both the result of the calculation and for the range being added; they're just not above each other.
I also used absolute column and cell references by putting a $ in front of whatever column or row should stay the same.
This let me drag the cell to create the calculation for every summary cell in the summary section.
If you'd like to play with the spreadsheet, here it is. Right-click on the link and choose to save it to your computer.