# hpr2069 :: Counting Stuff in LibreOffice Calc

### I try to explain how to use the COUNTIF function in LibreOffice to generate reports

<< First, < Previous, , Latest >>

Hosted by Jon Kulp on 2016-07-07 is flagged as Clean and is released under a CC-BY-SA license.
LibreOffice, formulas, tips and tricks. 1.
The show is available on the Internet Archive at: https://archive.org/details/hpr2069

Listen in ogg, spx, or mp3 format. Play now:

Duration: 00:19:55

### Part of the series:LibreOffice.

In this in-depth series on LibreOffice we examine Writer, Calc and Impress

When I took over as Director of the School of Music in January, one of the first things I did was to try to get a better handle on the number of faculty I had at various ranks, how many had terminal degrees, how many already had tenure, how many were on tenure track, how many held endowed professorships, and so forth. Somewhere in the process, I discovered a handy trick for generating reports for this kind of thing. It's the `COUNTIF` function of LibreOffice calc. In this episode I will go through some examples of ways that I've used `COUNTIF` to generate reports.

## Examples

Count occurrences of the string from A6 of current sheet on other sheet Personnel in column K

`=COUNTIF(\$Personnel.\$K\$1:\$K\$135,Reports.A6)`

Count occurrences of explicit string on other sheet "Personnel" in column K

`=COUNTIF(\$Personnel.\$K\$1:\$K\$135,"=Instructor")`

Count greater than or equal to 50

`=COUNTIF(\$I\$2:\$I\$105,">=50")`

Using `SUMPRODUCT`, count between range greater than or equal to 40 but less than 50

`=SUMPRODUCT(\$I\$2:\$I\$105>=40,\$I\$2:\$I\$105<50)`

Look for the string from sheet "Reports," cell A21, in the sheet "Personnel" column U, excluding any rows that have the value "Adjunct" in column K.

`=COUNTIFS(\$Personnel.\$U\$1:\$U\$135,Reports.A21,\$Personnel.\$K\$1:\$K\$135,"<>Adjunct")`

## Show Transcript

Automatically generated using whisper

``whisper --model tiny --language en hpr2069.wav``

You can save these subtitle files to the same location as the HPR Episode, and they will automatically show in players like mpv, vlc. Some players allow you to specify the subtitle file location.

<< First, < Previous, , Latest >>

#### Thanks for the quick tips

I already know about countif. There is also a function called sumif, which is similar. Instead of counting, it will sum up the values of a given range if criteria is met in another range.

Consider:

red | 1 blue | 4 green | 6 red | 4

=sumif(A1:A4,"red",B1:B4) result: 5

The sumproduct was new for me. I have already started to use it. Keep up the informative shows.

## Leave Comment

Note to Verbose Commenters
If you can't fit everything you want to say in the comment below then you really should record a response show instead.

Note to Spammers
All comments are moderated. All links are checked by humans. We strip out all html. Feel free to record a show about yourself, or your industry, or any other topic we may find interesting. We also check shows for spam :).

Provide feedback
 Your Name/Handle: Title: Comment: Anti Spam Question: What does the letter P in HPR stand for? Are you a spammer? Yes No What is the HOST_ID for the host of this show? What does HPR mean to you?