COUNTIF()

Here is an example of the COUNTIF() function in Open Office Calc. I have set up a spreadsheet that contains them model and color of various cars. We will use Validity from the Data menu and the COUNTIF() function to determine number sold.

Here is a screen shot of my spreadsheet.



You can type the two columns like I have it or you can use your own data. Column A contains the model of different cars and column B contains the color.

Click on cell D2 and click Validity on the Data menu. When the dialog box comes up, select List from the drop down box next to Allow on the Criteria tab and type in the different models.

Next, click on cell D5 and do the same thing. But type in the different colors. Note: You do not have to type them in alphabetical order. If you want the list to appear in alpahbetical order in the drop down list in the cell, simply check Sort entries ascending.

Here is a screen shot for the models list.



It is now time to enter the COUNTIF() function.

COUNTIF(range, criteria)
range = What yo want to search through.
criteria = What you want to count.

Now, click on cell E2 and enter the following formula.
=COUNTIF(A2:A21;D2)

Now, click on cell E5 and enter the following formula.
=COUNTIF(B2:B21;D5)


You are now ready to use your worksheet. Cells D2 and D5 have drop downs. Just select in one of those cells and drop down arrow will appear. Click on the drop down arrow and select what you want to count.


If you have a large amount of data for your list, instead of typing each entry, you can reference a cell range. See my videos on You Tube.

Video #1 - This video shows how to use Validity in Data menu by referencing a list on another sheet in the same file.

Video #2 - This video shows how to use Validity in the Data menu referencing a list on a sheet in another file.

Hope this helps. If there is a certain feature you would like explained in Calc please leave comments explaining what you would like.