OpenOffice Calc
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