Excel – How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

If you can add some intermediate formulas to your sheet, here's a possible solution.

Based on the ranges in your example:

Put in cell N123 This returns the part to the left of , or the whole cell if no ,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Put in cell O123 This returns the part to the right of , or - if no ,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Put in cell P123 This returns 1 if cell N123 is unique in the list, plus 1 if cell O123 is unique

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Copy cells N123..P123 down to N123..P127

The number of unique items is =SUM(P123:Q127)

This assumes there are at most 2 items in each data cell. If this is not the case you will need to add more intermediate columns, and extend the formula in O123 accordingly. If there are many more than 2, this will quickly get out of hand.

FWIW, you say a VBA solution is not posible, but is a VBA user defined function ok?

It would look something like =CountUnique(M123:M127)

As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:

=ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371

The result is in kilometers, for small distances I used *1000 to display the distance in meters.

  |        A        B      C
--|--------------------------
0 |      LAT      LON   DIST
1 | 52.39964 13.04729               
2 | 52.39985 13.04802   54.8
3 | 52.40116 13.04744  150.9
4 | 52.40147 13.04722   37.6
5 | 52.40163 13.04685   30.8
6 |      ...      ...    ...

To display distances in miles, substitute *6371 with *3958.