https://entorb.net//wiki/index.php?action=history&feed=atom&title=OpenOffice_CalcOpenOffice Calc - Revision history2026-05-06T10:24:34ZRevision history for this page on the wikiMediaWiki 1.43.1https://entorb.net//wiki/index.php?title=OpenOffice_Calc&diff=4933&oldid=prevTorben at 20:56, 30 October 20242024-10-30T20:56:21Z<p></p>
<p><b>New page</b></p><div>[[Category:Software]]<br />
See [[Excel]] as well<br />
Open Office / Libre Ovvice <br />
oocalc / localc<br />
<br />
===Matching===<br />
A1:A10 = ""<br />
A1:A10 <> ""<br />
<br />
===CountIf with joined criteria of 2 columns===<br />
I want to join 2 CountIf like<br />
=COUNTIF(A1:A10,"j") <br />
=COUNTIF(B1:B10,"Dresden")<br />
--> =COUNTIFS(A1:A10,"j",B1:B10,"Dresden")<br />
or <br />
--> =SUMPRODUCT((A1:A10="j"),(A1:A10="Dresden"))<br />
<br />
===Last Line Contents===<br />
Fetch contents (numbers) of last line (column D here)<br />
=LOOKUP(1E+099;D5:D9999)<br />
<br />
===Count Items in Date Range===<br />
=SUMPRODUCT(A1:A9999>(B1-14))<br />
<br />
===Count Row A if Row B matches sth===<br />
=SUMPRODUCT(B2:B999;A2:A999>(A2-14))<br />
<br />
===Histrogram===<br />
see [https://help.libreoffice.org/Calc/Array_Functions#FREQUENCY]<br />
<br />
===Ranking (Sport etc)===<br />
C holds the points. How many competitors have less points: (=your rank)<br />
1+COUNTIF($C$2:$C$999,">"&C2)<br />
<br />
===Pivot Tables===<br />
see [https://help.libreoffice.org/Calc/Select_Source]<br />
<br />
==Strings==<br />
Substring, starting at ": ", having max 32 char length<br />
=MID(D4;SEARCH(": " ; D4)+2 ; 32)<br />
<br />
==Time and Date==<br />
===Days between Dates===<br />
=A1-A2<br />
=DATEDIF(A2;DATE(2013;12;31);"d")<br />
<br />
===last / first day of month===<br />
# last day of current month<br />
=EOMONTH(A2;0)<br />
# first day of current month = last of previous + 1 day<br />
=EOMONTH(A2;-1)+1</div>Torben