Despite some custom software available, Microsoft Office provides a very convenient solution for the management of sand collections: the classical MS Excel. Working with tables in Excel is really practical especially if you make full use of its very powerful functions. There are plenty of them available but the one I’d like to talk about is COUNTIF.
By definition, COUNTIF function “counts the number of cells within a range that meet the given criteria”. It looks something like COUNTIF(range,criteria), where range is the column or row of interest and criteria can be a number or another cell. How can this be useful? Well, how about the question: “how many sands do I have from country X?” or “what countries am I still missing in my collection?”. Using COUNTIF is very easy to find the answer automatically: just assign the function range to the column where you have the countries’ names (e.g. A1:A300) and for criteria just type the name of the country you are interested in. For even better results, create a separate column where you list ALL the countries in the world (let’s say column C) and put in the column D (in cell D1) the following formula: COUNTIF(A1:A500,C1).You will have thus an automated list of countries and the number of sands from each individual country (you need to be careful that all countries are spelled correctly, otherwise they won’t appear in statistics).
In what it concerns me I went further with using the COUNTIF function and recorded also the first administrative units from each country. This may be useful for those collectors who aim, for example, at collecting at least one sand from each administrative unit in the world. The problems may arise from the way how some regions and provinces are spelled out (especially in languages using diacritics). My solution for this is to use unique two-letter codes for both country and region (here I would highly recommend the www.statoids.com website).
The results look like in the image above, with countries and their subdivisions organized alphabetically on columns. I put all the info on a separate tab within the same worksheet for very quick access to the data and I created separate tab only for countries in case that I need an even faster response. All basic data are on tab ‘Sand Atlas’ and, if you select a very wide range (in my case A1:A100000), any new entry will be automatically added to the statistics so you don’t need to worry about anything. This method allows me to know just with a quick glance that among Spanish provinces, for example, 30 sands are from Canary Islands (ES-CN code) or 12 from Andalusia (ES-CN), but none from Aragon, Navarra, Ceuta etc.
PS: For fellow collectors using MS Excel in German language, the function corresponding to COUNTIF( ) is ZÄHLENWENN( ).