« Good information on the OpenOffice.org documentation wiki about regular expressions in Calc and Writer | Main | Resizing the slides in OpenOffice Impress Handouts »

February 11, 2008


TrackBack URL for this entry:

Listed below are links to weblogs that reference A nice little Calc spreadsheet function: SUMIF (works in Excel too):


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.

Wouldn't it in this very case be more appropriate to go with pivot tables / data pilots?

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.


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.



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:

Not sure if any of this helps you.


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?


Hi Kate,

"list" = sheet

Is there a way to use 'sumif' based on cell formating? eg a number being for bold?

The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog