I have an Apache OpenOffice Calc spreadsheet that I use to track deposits to bank accounts. I loaned a friend money some time ago for a used car; when I get a check from him, I note it in a worksheet in the Calc workbook. I also get checks from him sometimes unrelated to the auto loan repayment and have received some checks that include the auto loan payment plus reimbursement for other expenses. I have a "note" column in the worksheet where I'll always have "75", if the check applies to the auto loan repayment, though there may be other text before and after the "75" in the cell. So I thought it would be easy to count the number of checks I've received from him that apply to the auto loan repayment by using the formula below, since the note column is column F and the deposits start in row 2 (the first row is a heading line) and go through row 32:
=COUNTIF(F2:F32,"*75*")
That works in Microsoft Excel, but it doesn't work in OpenOffice Calc (I'm using version 4.1.1). The count is always zero, if I use the formula above. I found I have to use the formula below, instead, which correctly reports 21 occurrences of 75 in column F at the moment:
=COUNTIF(F2:F32;".*75.*")
Excel will treat the first asterisk as representing a character or any number of characters before the "75" and the asterisk after "75" as representingany number of characters appearing in the cell after "75". However, Calc doesn't view the asterisks that way. Instead, I need to use a regular expression. In a regular expression (regexp), a period represents any character and an asterisk represents zero or more occurrences of the preceding element. For example, ab*c matches "ac", "abc", "abbc", "abbbc", and so on. So the ".*" represents any character appearing zero or more times in the cell. If I try using the formula I use in Calc in Excel, it will report zero occurrences just as Calc reports zero occurrences when I use the formula that works in Excel in Calc.
Since I want to obtain the total amount he has repaid so far, which is the count of the number of occurrences of 75 in column F by $75, I use the forumula below in Calc to obtain that amount, which returns 1575 as of today:
=COUNTIF(F2:F32;".*75.*") * 75
