First time here? Check out the FAQ!
asked 2012-02-27 07:08:43 +0200Hylton Conacher
Using LibreOffice Calc 3.3.1
On a sheet I have a block of numbers spanning a few columns and rows. Numbers can either be single or double digit and are in a range of 0 to 100 however reading the rows or columns will NOT result in sequential numbers and there is no pattern ie
1 51 6 2 11 23 5 9 1 2
Numbers never repeat on a row.
In a cell below the table or on another sheet I would like to find the total count of several of the numbers in the above table eg 1 and 2, however my count must only include values specified i.e. if I specify to search the above block A1:E2 and count the number 1, then the answer would be 2 instances i.e. not include 11, 51. If I specify the number 2, then again the answer would be 2 because my search would have excluded 23.
I have tried COUNT and FIND but have not been able to work out the formula.
Any assistance appreciated.
answered 2012-02-27 12:10:23 +0200MegaTallDave
Construct a row of number you are looking for.
Below this row, use the countif function to find all the matches in your data.
For instance, if your data started at cell b2 and ended at cell u4, and your first number in our first new row was b5, then you would use something like,
Then you grab the little black box on the lower right hand corner and extend this formula. (I'm very informally taught on spreadsheets, so I have no idea what doing that is actually called).
Make sure you use absolute references (the dollar signs) when referencing your data since extending the countif to point to the other items in your row of numbers to search would otherwise also shift where Calc looks for data.
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-02-27 07:08:43 +0200
Seen: 846 times
Last updated: Feb 27 '12