Using SUMIF() in OpenOffice Calc spreadsheets

SUMIF(), like other functions, works the same way in Calc as in Excel. However, it's a powerful function that I want to make sure readers know about, if they don't already.

Let's say you keep a spreadsheet full of various numbers, and they fall into a few categories. You have a bunch of things that are categorizable as:

financial items such as business expense ,donations; mortgage interest

Or perhaps
client activities such as calls to this customer; calls to another customer; calls to yet another customer

Or
expenses for various hobbies like 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. And you don't want to re-sort them to put the categories together because you're always entering new stuff, or you're sharing the spreadsheet--or you just don't want to.

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.

=SUMIF($A$13:$A$22;$A6;E$13:E$22)

This lets 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.

---

---

Comments

[lowoijo]

i already use Open Office version 3.0 , but why open office more needed Ram in my computer ?. as a consequence my computer hang, i hope next version open office is more eficien ram and more faster. Thanks

[Vincent Ranger]

Hi,

Thanks for this blog. But, I think that the function is not as well as MS Excel. Maybe you can help me (I trying to use only OpenOffice now!).

In MS Excel, I am able to put two conditions for the SUMIFS, but not in OpenOffice. Is there a another function for that ? (Example : I want to do the sum of C1:C10 if A1:A10 = 10 and if B1:B10 = 7).

Can you help me ?

Thank you!

Vincent

[Solveig]

Hi Vincent,

I think you could use IF() within SUMIF() -- or perhaps vice versa -- to get the same functionality.

[Vincent Ranger]

I don't understand how ? (I've try some possibility...)

Vincent

[Solveig]

Hi Vincent

You would group it like this. Use IF as the grouping function and then put in the test, then the sumif to do if it's true, and the sumif to do if it's false.

=IF(sum(a1:10)=10);SUMIF(A1:B6;A6;B1:B6);SUMIF(A1:B6;A3;B1:B6))