Re: [libreoffice-users] Counting Instances

On 30/12/13 09:47, doug wrote:
> On 12/29/2013 05:10 PM, Jay Lozier wrote:
> > On 12/29/2013 03:12 PM, Keith Bates wrote:
> > > Hi everyone,
> > >
> > > I've used Libre Office for years, but I am an absolute newbie as far 
> > > as Calc is concerned for anything but the very basics. :)
> > >
> > > Here is my problem.
> > >
> > > My church has to report to the copyright licence holders the names 
> > > of the songs it uses and the number of times each song is used in a 
> > > given time period.
> > >
> > > The software we use for projecting the words for songs (OpenLP) will 
> > > produce the name of each song used in each service. It will generate 
> > > a CSV
> > >
> > > Obviously it is easy enough to import that into a spreadsheet.
> > >
> > > How can I then count the number of times each song is used? I could 
> > > sort the table by song title and then count them manually, but I am 
> > > guessing there would be some sort of function that automates the 
> > > counting.
> > >
> > > Thanks for any help on this.
> > Kieth
> >
> > I would try the COUNTIF(). The syntax is =COUNTIF(range, criteria). 
> > The range is the column with the titles and the criteria is the title 
> > you want counted. I would set up the data one sheet and in another I 
> > would set a column with each title and in the next column have 
> > =COUNTIF(datasheet.range, cell). The only problem is if you 
> > accidentally missed of the titles in the range. Assuming an average 
> > of 3 services per week that use 5 songs you have about 780 separate 
> > instances that a song was used. I can see easily missing a single title.
> >
> > An alternative is to use Base with the data uploaded to a table. CSV 
> > files work very well with databases. Then the have Base count each 
> > title. Most people find databases more intimidating because they do 
> > not directly work them. But once you get familiar with them you might 
> > find this an easier approach for problems like this.
> >
> > I tend to use raw SQL when working with Base (mostly because I use 
> > SQL at work). In SQL the query would look like this:
> >
> > SELECT Count(*) AS Count <AS count is optional>
> > FROM  Songs <use actual table name>
> > GROUP BY Title <use actual column name which has the titles>
> >
> > If you want you can send me a typical dataset to off list and I will 
> > look at both with Calc and Base.
> >
> > Jay
> I'm wondering if you are expected to pay royalties on the music. If 
> so, I would think that there is
> enough sacred music in the public domain that you could forget about 
> the problem, and save
> money for the church to use for religious purposes. You should have 
> your music director look into that.
> --doug
The licence is through CCLI (www.ccli.com) and is a flat fee based on 
the size of the congregation. The total fees are divided amongst the 
song authors based on total song usage.

For contemporary music it is a very cost effective way of legally 
reproducing lyrics and music.


--
God bless you
Keith Bates
4 Mooloobar St
Narrabri NSW
Ph 02 67924890

Jesus is the Way
the Truth and the Life


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted