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
or
toys; paints and crayons; DVDs; nap supplies
or
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.
=SUMIF($A$13:$A$22;$A6;E$13:E$22)
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.
I had no idea you were into Improv and Music Festivals. This is totally unrelated, but I can't figure out how to make negative dates, ones that end in B.C.
Posted by: spayced | February 11, 2008 at 06:07 AM
Wouldn't it in this very case be more appropriate to go with pivot tables / data pilots?
Posted by: KaTe | February 11, 2008 at 11:30 PM
Hi KaTe,
That would be one way to go, but a little more complex. Just depends on how you like to work and what other data you'd like to see, like narrowing down to expenditures by month.
Solveig
Posted by: Solveig | February 12, 2008 at 08:20 AM
Hi spayced,
I must admit I'm stumped on negative dates, too. Nothing in the help that I could find in a quick search and google didn't really give me anything useful either.
solveig
Posted by: Solveig | February 12, 2008 at 08:26 AM
spayced,
The only way I've found (so far) to deal with B.C.E. dates mathematically is to convert them to Julian Dates, perform whatever function on them and then convert them back.
At one point I was working on some custom functions to handle the Julian conversions —to go along with some other custom date functions—but have not finished those particular ones yet.
Andrew Pitonyak's book on macros does mention B.C.E (page 28 - do a Google Book search and you can preview it) but doesn't seem to go into any detail. I believe Andrew is active on the OpenOffice macro forum. He might be kind enough to offer further insight.
If you enter -693,595 into a cell and format it as a date will give you January 1, 0001.
Unfortunately subtracting further from that date always seems to return the year 1.
If you're not familiar with Julian Dates a straightforward explanation may be found at:
http://aa.usno.navy.mil/data/docs/JulianDate.php
Not sure if any of this helps you.
Regards,
Dave
Posted by: Dave | February 12, 2008 at 02:43 PM
Hi Kate,
My cels range of code of article is a1:a10 but in 10 lists (list1:list10)! ; category is in cel List11.a1; cels range of numbers of article is b1:b10 but again in 10 lists (list1:list10).
How J must wrote this sumif function in this case?
Thanks
Posted by: Sevalav | February 06, 2009 at 06:07 PM
Hi Kate,
"list" = sheet
Posted by: Sevalav | February 07, 2009 at 05:45 PM
Is there a way to use 'sumif' based on cell formating? eg a number being for bold?
Posted by: Ian | March 25, 2009 at 09:51 PM
http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/
Posted by: wow power leveling | July 05, 2009 at 11:15 PM