OpenOffice Calc

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

Matching

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

CountIf with joined criteria of 2 columns

I want to join 2 CountIf like

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

Last Line Contents

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

=LOOKUP(1E+099;D5:D9999)

Count Items in Date Range

=SUMPRODUCT(A1:A9999>(B1-14))

Count Row A if Row B matches sth

=SUMPRODUCT(B2:B999;A2:A999>(A2-14))

Histrogram

see [1]

Ranking (Sport etc)

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

1+COUNTIF($C$2:$C$999,">"&C2)

Pivot Tables

see [2]

Strings

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

Time and Date

Days between Dates

=A1-A2
=DATEDIF(A2;DATE(2013;12;31);"d")

last / first day of month

# last day of current month
=EOMONTH(A2;0)
# first day of current month = last of previous + 1 day
=EOMONTH(A2;-1)+1