Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 13 Apr 2019 15:26:02 +0200How do I find the last entry in a column that may contain many blanks (Solved)https://ask.libreoffice.org/en/question/190297/how-do-i-find-the-last-entry-in-a-column-that-may-contain-many-blanks-solved/I wish to easily find the last value in a column that may contain blank entries. I am allowing up to 70 rows of data.
Column A will contain the Date of an entry, but not every other column will have an entry on that date.
I would like to find a way to determine what row contains the last entry in Column B, so I can select the corresponding date in Column A
In the accompanying image -The range is A10:D79. Column A will contain consecutive rows of data (presently to Row 15) the last entry in Column B is in Row 13. How do I find the row number of that value?
![image description](/upfiles/15551604027861383.png)
I want the row number of the range A10:D79 that would correspond to that circled entry (relative row 4) so I can use it, along with the offset (+9) to determine the actual row number (13) in that sheet. Then I can use the indirect address function to extract the required value for the date on that row.
Other related questions seem to assume that ALL columns in any row are filled, and do not seem to address my particular need where there are many blank rows that are irregularly spaced in the target column.nobody_specialSat, 13 Apr 2019 15:26:02 +0200https://ask.libreoffice.org/en/question/190297/Count cells containing certain texthttps://ask.libreoffice.org/en/question/187938/count-cells-containing-certain-text/How can I count the cells in a range who's contents are contained in a larger string?
=COUNTIF(FIND(D3:W114,C1))
returns #VALUE!, or ERR:511 as an array formula
the cells are blank or have a letter.
Application: Count railroad cars by destinations in a yard. A single character indicates the destination, and I want to know how many cars I have for a train I must build which brings cars to multiple destinations. Next I want to highlight those cars (cells), to help sort.[C:\fakepath\WO.ots](/upfiles/15534624713801615.ots)Henrik PedersenFri, 22 Mar 2019 21:57:07 +0100https://ask.libreoffice.org/en/question/187938/Calc Filter Results Counthttps://ask.libreoffice.org/en/question/5050/calc-filter-results-count/When using auto-filter or standard filter in Calc is there any easy way to see how many rows are being displayed that match the specified filter settings?
I have found a workaround which is to right click on the Sum indication on the status bar at the bottom and change it to CountA then select a column of the results where every row has a value in. This seems to show how many rows there are but seems a bit clumsy is there a better way?ecotenSun, 19 Aug 2012 17:19:43 +0200https://ask.libreoffice.org/en/question/5050/Count rows for which certain columns differhttps://ask.libreoffice.org/en/question/164816/count-rows-for-which-certain-columns-differ/ I have a table, where two columns contain similar data. I want to count the number of rows, where these are identical.
I found a solution, but it requires adding an extra row: suppose the two columns to compare are A and B, I add another column C with `=(A1 = B1)` and so on and can then use `=COUNTIF(C1:Cn, "TRUE")`.
Is it possible to count rows with identical columns without adding such an extra column? I tried using `COUNTIFS`, but I can't seem to get the syntax right.crater2150Wed, 05 Sep 2018 14:58:57 +0200https://ask.libreoffice.org/en/question/164816/Use SUMPRODUCT(ISBLANK()) to count alternated cells range.https://ask.libreoffice.org/en/question/141159/use-sumproductisblank-to-count-alternated-cells-range/ Hi, I'm trying to count the blank cells using the formula =SUMPRODUCT(ISBLANK()), but I realized that I can only count consecutive cells like =SUMPRODUCT(ISBLANK(V7:V21)) and what I need is to count alternated blank cells like (V7;V9;V11;V17;V19;V21).
Is it possible?wlldmTue, 19 Dec 2017 16:10:05 +0100https://ask.libreoffice.org/en/question/141159/Calc: Pivot table: create: 'guess' function for data fieldhttps://ask.libreoffice.org/en/question/101486/calc-pivot-table-create-guess-function-for-data-field/Can we please have a feature such that, when you create a pivot table with some non-numeric column, Calc doesn't somewhat stupidly 'guesses' the function will be "Sum", which is not possible for non-numeric data, but switches to "Count" instead?
Thanks for your consideration, RZRobertZimbardoMon, 19 Jun 2017 20:34:52 +0200https://ask.libreoffice.org/en/question/101486/Count frequency of text in spreadsheethttps://ask.libreoffice.org/en/question/85128/count-frequency-of-text-in-spreadsheet/How do you count the frequency of a word appearing in a spreadheet?
E.G.
My spreadsheet lists several institutions and the last time or next time they were or should be "Checked"
There are 4 columns with months (January-December) listed for reference on when each item should be checked.
How do I search the spreadsheet and get a sum of how many times "January" for example shows up in the sheet?
I have tried the SumIF and CountIF functions and I keep getting error messages in response in both in Excell and Open Office Calc.
I have also tried the "Find All" function which highlights everything but wont quantify...
This is such a simple function I was hoping for a simple way to get to it but have been having so much trouble.
(I am also a spreadsheet newbie- simple walkthroughs appreciated)KevynElizabethThu, 05 Jan 2017 19:47:03 +0100https://ask.libreoffice.org/en/question/85128/function didn't work: copied to cell, it did work. wtf?https://ask.libreoffice.org/en/question/93453/function-didnt-work-copied-to-cell-it-did-work-wtf/ I had the function sum(c39:c48) in cell c38. I noticed that the answer in cell c38 was wrong, and no matter what I did it wouldn't change. (All other cells were recalculating, just not cell c38.) I copied the entire range c38:c48 to f38:f48 and back again and everything worked fine in the recopied c38.
Details: Calc: version 1:5.1.6~rc2-0ubuntu1~xenial1
OS: [Ubuntu 16.04.2 LTS _Xenial Xerus_ -Release amd64 (20170215.2)]/ xenial main restricted
Hardware: Dell Inspiron 3558
CPU: Intel i3-1515U @ 2.10 GHz x 4
Graphics: IntelĀ® HD Graphics 5500 (Broadwell GT2)
Is it recalculate? Is it a one-off bug? Is it my copy? Do I have a mismatch of something somewhere?
I've got a similar problem with count() in another spreadsheet. I'm beginning to doubt the usefulness of LibreOffice Calc.sighthoundmanTue, 25 Apr 2017 18:49:43 +0200https://ask.libreoffice.org/en/question/93453/How can I count the number of occurences of individual Chinese characters in a document?https://ask.libreoffice.org/en/question/48634/how-can-i-count-the-number-of-occurences-of-individual-chinese-characters-in-a-document/I want to count the number of occurrences of each separate word or Chinese character in a document. The words have already been sorted into groups. I would like a word count of the words within that group. I am using LibreOffice (can be Writer or Calc). I would prefer not to do this in terminal mode.
For example:
the = 12,
with = 5,
take = 2.rahalverThu, 02 Apr 2015 08:33:20 +0200https://ask.libreoffice.org/en/question/48634/How can I automatically update the word count from a Writer doc to Calc?https://ask.libreoffice.org/en/question/70567/how-can-i-automatically-update-the-word-count-from-a-writer-doc-to-calc/Hey guys. I'm writing my master thesis in Writer and having a detailed project planning in Calc, where I am tracking how many words I wrote so far.
I was curious if there is any way of doing this automatically.
I found this answer for Microsoft Office and I was wondering if something similar can be done in Libre Office.
http://answers.microsoft.com/en-us/office/forum/office_2007-excel/automatically-add-word-count-of-msoffice-document/e43cb5bf-1d00-49a3-b765-9cc04661c1ce
Thanks,
MihaipummyFri, 27 May 2016 14:14:24 +0200https://ask.libreoffice.org/en/question/70567/Subtotals returns TRUE in a subtotals cellhttps://ask.libreoffice.org/en/question/69694/subtotals-returns-true-in-a-subtotals-cell/I'm trying to count how many adults there are in a given cell range. This is a column where the information "adult" or "child" (as string) is calculated with an "if" formula based on the birth date entered in another column.
The Data -> Subtotals function gives me TRUE instead of the number of cells where "adult" is.
I tried with several different counts (count, counta, countif, ...) but always the same result. This question has been asked before, but the answer did not help me.
Thank you.AndyfcWed, 11 May 2016 11:23:40 +0200https://ask.libreoffice.org/en/question/69694/how to count max string of successive zero'shttps://ask.libreoffice.org/en/question/68320/how-to-count-max-string-of-successive-zeros/i am analysing the seasonability of non-occurance (=0) . i have a table with 52 columns which represent the weeks of the year.
i would like to count the max string of successive zeros in a row to see how long there is no occurance.
how can i do this?
corjanFri, 15 Apr 2016 12:26:32 +0200https://ask.libreoffice.org/en/question/68320/Easy way to count search results in Calchttps://ask.libreoffice.org/en/question/61752/easy-way-to-count-search-results-in-calc/ Hello,
I'm doing a search in Calc to find all the instances of a string. I get the result list but there is no count of the instances matched!
You know, a "2 of 20 matches" thing that changes as I cycle through the results (like Firefox does).
Or even just the number of results ("20" in this example).
Is there some setting that I have missed or what? How does everyone else count their search results?
Sorry if this has been asked, but I'm genuinely baffled by the apparent lack of this information. It just makes no sense to not include that data as part of a search function...
Kind regards!funkybomberWed, 09 Dec 2015 03:00:08 +0100https://ask.libreoffice.org/en/question/61752/Count filtered rows with additional criteriahttps://ask.libreoffice.org/en/question/61520/count-filtered-rows-with-additional-criteria/Hi,
Looking for a formula to count the number of rows in a filtered list that also satisfy an additional, unfiltered, criteria.
In Excel I can do this by combining the SUMPRODUCT and SUBTOTAL functions, as per the following .....
=SUMPRODUCT((LEFT(BH2956:BH3964)="T")*SUBTOTAL(3,OFFSET($A2956:$A3964,ROW(C1:INDEX($C:$C,ROWS($C2956:$C3964)))-1,,1)))
This doesn't work in Calc. Instead of the correct answer 53, it gives 411866. TBH I don't understand how the Excel formula works - it was offered as a solution on one of the Excel forums. Hoping there's perhaps a better way to accomplish this in Calc. Thanks.
redartWed, 02 Dec 2015 16:08:15 +0100https://ask.libreoffice.org/en/question/61520/character "0" wrong counthttps://ask.libreoffice.org/en/question/46543/character-0-wrong-count/Hello,
I'm trying to count the "0" characters in a column.
In this column the formula can answer "0", "1" or "2".
The are some merged cells between some of the regular ones. They are filed with text (no "0" characters here)
Here is my formula :
=NB.SI(K9:K186;"0")
**BUT**
It counts the fused ones as "0". I verified, there are 102 "0" cells but it says 107, as if the fused title cells between the other ones contained "0".
Why ?
JauFri, 20 Feb 2015 19:35:51 +0100https://ask.libreoffice.org/en/question/46543/Counting number of times two adjacent cells match a certain criteriahttps://ask.libreoffice.org/en/question/43574/counting-number-of-times-two-adjacent-cells-match-a-certain-criteria/Hello, I'm looking for a way to count the number of times two adjacent cells in two separate columns match. In this example, column A contains vehicle types ("SUV", "Sedan", "Truck"), and in column B contains vehicle color ("Red", "Blue", "Black").
I want to count the number of black SUVs:
A B
1 SUV Red
2 Truck Blue
3 Sedan Red
4 SUV Black
5 Sedan Blue
6 Truck Blue
7 SUV Black
In this case, the function should print 2.
Is there a way to do this?MrMusAddictFri, 12 Dec 2014 21:55:46 +0100https://ask.libreoffice.org/en/question/43574/create chart from values single columnhttps://ask.libreoffice.org/en/question/41969/create-chart-from-values-single-column/I have a Country column where country names are stored.
My aim is to generate a bar chart where the number of occurrences for each of those country names is graphed.
How can I turn a series of values in a given column into a chart?
Running LibreOffice 4.3.0.4 on Mac OS X 10.10 Yosemite.
Screenshot:
https://www.dropbox.com/s/2vi07q494y8smca/Screenshot%202014-11-02%2022.32.53.png?dl=0manchineMon, 03 Nov 2014 02:36:37 +0100https://ask.libreoffice.org/en/question/41969/Count columns in Calchttps://ask.libreoffice.org/en/question/41931/count-columns-in-calc/hello, in Calc ver. 4.2.6, if I select a number of columns, is there a way to count them other than manually? i.e. there are a lot of columns in this file and I just wanna know how manybennypr0faneSun, 02 Nov 2014 00:23:16 +0100https://ask.libreoffice.org/en/question/41931/Counting how many times a certain number occurs in a list of cells in Libre Office Calchttps://ask.libreoffice.org/en/question/38098/counting-how-many-times-a-certain-number-occurs-in-a-list-of-cells-in-libre-office-calc/I have a few cells that are filled with random numbers, eg:
cell A1: 3937376385699290
cell B1: 6568408355712890000
and I want Libre Office Calc to tell me how many 1's, 2's, 3's, 4's, etc are all together in those 10 or so cells.
So in the case of the above two cells, there are 1 1's, 2 2's, 5 3's, and so on
I tried COUNT and FIND but they didn't work
All help appreciated :)MJKSun, 10 Aug 2014 07:05:52 +0200https://ask.libreoffice.org/en/question/38098/How to Sum only if range totals greater than 3https://ask.libreoffice.org/en/question/37366/how-to-sum-only-if-range-totals-greater-than-3/hi, Im trying to create a field totalling how many "triple play" orders I've gotten throughout the day. So far Ive done some pretty decent formulas to keep track of my sales, but this particular one eludes me.
If the Cable, Tv, and Phone cells each have a 1 in them, then I'd like the Triple Play Totals cell to totall how many rows (accounts) have all three of those cells marked. If a row (account) doesnt have all three marked with 1's then the Triple Play Totals field doesnt add them in the total.
I hope this makes sense, any advice is appreciately very much.
netsharkTue, 22 Jul 2014 20:57:33 +0200https://ask.libreoffice.org/en/question/37366/How to count TRUE entries in a columnhttps://ask.libreoffice.org/en/question/16615/how-to-count-true-entries-in-a-column/A spreadsheet has only the entries TRUE and FALSE.
<pre>
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE</pre>
Which has been set up by entering TRUE in the top position an pulling it down into the rest of the column. To count the number of TRUE entries, which here is 5, the formula =COUNTIF(A1:A6,TRUE) applied to the column should work, but it always returns the result 1. On the other hand, the formula =COUNTIF(A1:A6,FALSE) works correctly on a similar column got by pulling down FALSE.
If a column is set up by actually entering the entries one after another, say
<pre>
TRUE
FALSE
FALSE
TRUE
TRUE</pre>
COUNTIF() works properly. I suspect that the problem is that LibreOffice is entering different **kinds** of TRUE, because when I apply Data->Filter->Standard_Filter to the first column (all TRUE), I see a large number of entries all TRUE.
What's going on? Is there any way to set up the column correctly? "Pulling down" TRUE() seems to have its own problems.JonryshThu, 25 Apr 2013 22:19:40 +0200https://ask.libreoffice.org/en/question/16615/Most frequent words in a columnhttps://ask.libreoffice.org/en/question/5478/most-frequent-words-in-a-column/Hi
What i'm trying to do ist this:
I want to evaluate my performance in a game where in each match i play a different character. Now i enter each Character manually in a column and want to extract what character(s) I play most often.
Is there a way to get the string that occurs most often in that column?
Thanks in advance,
TrichlorethanTrichlorethanSat, 01 Sep 2012 13:17:56 +0200https://ask.libreoffice.org/en/question/5478/count entries in named column of a rangehttps://ask.libreoffice.org/en/question/14652/count-entries-in-named-column-of-a-range/I have a standard spreadsheet with Jan, Feb, Mar etc headings for the whole year. I want to count the number of entries under the "Mar" heading, for example (within a defined range).RossMunroFri, 15 Mar 2013 09:40:04 +0100https://ask.libreoffice.org/en/question/14652/count cells in a range in macrohttps://ask.libreoffice.org/en/question/4942/count-cells-in-a-range-in-macro/
I have a cellrange and I'm trying to get the number of cells populated in that range.
oCellRange = rr_sheet.getCellRangeByName("a63:b68")
I tried count=oCellRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), but that gives the total number of rows in the range, I just want the ones that have data.
I tried the above call with COUNTA, but that complained and said it was not found. I could write a little function that loops through the DataArray, but I was trying to avoid that.ptn4eglFri, 17 Aug 2012 02:58:03 +0200https://ask.libreoffice.org/en/question/4942/How to count blank cells - formerly: Check if within a range of cells (a row) there is at least one blank cellhttps://ask.libreoffice.org/en/question/11565/how-to-count-blank-cells-formerly-check-if-within-a-range-of-cells-a-row-there-is-at-least-one-blank-cell/Hi there,
I'm trying to find a formula to check if within each row there is at least one blank cell.
Im using ISBLANK and OR.
If I do =OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2)...) and it works
but since my spreadsheet get till column FF, it is a very long formula and I would like to shorten it using ranges capabilities of libreoffice.
I've tried =OR(ISBLANK(A2):ISBLANK(FF2) but got an error E:502
I've tried =ISBLANK(A2:FF2) but got always FALSE
Anyone can help?
Thanks so much
miromiromarchiThu, 07 Feb 2013 17:39:44 +0100https://ask.libreoffice.org/en/question/11565/