OpenOffice Calc

From Torben's Wiki

See Excel as well Open Office / Libre Ovvice oocalc / localc


A1:A10 =  ""
A1:A10 <> ""

CountIf with joined criteria of 2 columns

I want to join 2 CountIf like

--> =COUNTIFS(A1:A10,"j",B1:B10,"Dresden")
--> =SUMPRODUCT((A1:A10="j"),(A1:A10="Dresden"))

Last Line Contents

Fetch contents (numbers) of last line (column D here)


Count Items in Date Range


Count Row A if Row B matches sth



see [1]

Ranking (Sport etc)

C holds the points. How many competitors have less points: (=your rank)


Pivot Tables

see [2]


Substring, starting at ": ", having max 32 char length
=MID(D4;SEARCH(": " ; D4)+2 ; 32)

Time and Date

Days between Dates


last / first day of month

# last day of current month
# first day of current month = last of previous + 1 day