How to count the number of occurrences of something in an Excel column

Recently a colleague produced a spreadsheet that was essentially acting as a planner something like the screen shot shown below:

My colleague then wanted to know how many days each member of staff had been allocated in the plan and how many staff members had been allocated to each week – essentially wanting to know how to dynamically count the number of x’s in each column/row. The answer lies in the Excel COUNTIF function:

=COUNTIF(range,criteria)

where range is the range of cells you want to search in whilst criteria is what you are looking for.

So simply by clicking in the cell N2 and entering the formula =COUNTIF(B2:M2,”x”) as shown in the screen shot below, the formula returns a 1 which is correct:

This formula is simply saying move through each of the cells B2, C2, D2 etc. to M2 and count how many times x appears. Notice that the COUNTIF function is case-insensitive but DOES require that strings are enclosed in quotation marks (“”). Simply by cutting and pasting we can fill in all the other totals for the employees as shown below:

Finally, we just need to repeat for the weeks by clicking in cell B22 and entering the formula =COUNTIF(B2:B21,”x”) as shown in the screen shot below:

Again, a simple cut and paste exercise along the row and you now have a dynamic count of the number of x’s in the rows/columns:

Hope this helps – as always all comments (positive or negative) are always welcome.

Drop Down Lists In Cells In Excel

Frequently, I need to produce Excel Worksheets that have drop-down lists inside cells. If you ever need to do the same and can’t remember how – this is for you. The screenshots are taken from Excel 2007 but I have included references for Excel 2003 as well.

Personally, I always prefer to put the data that builds my drop-down lists on a separate worksheet. So create a new worksheet and rename it to Lists or something and open that worksheet. Now, lets say for argument you wanted three drop-down lists as follows:

Services: WAN; Email; Desktop; LAN; Applications

Categories: New User; Delete User; Change of Permissions; SAP; Exchange; Outlook

Severities: Low; Medium; High

On your Lists worksheet put these lists into three columns and give them headings as shown below:

Lists Worksheet

Now select Insert > Name > Define (if you are using Excel 2003) or Formulas > Define Name (if you are using Excel 2007). The Define Name window will appear as shown below:

Define Named Range

Select the cells that this range refers to (in this case =Lists!$A$2:$A$6) and click OK. Repeat the exercise creating a named range for Categories and Severities.

Now click back to you Main worksheet which for sake of arguement looks like the worksheet shown below:

Main Worksheet

Select the cell you want the first drop-down list to appear in (in this case C2). Select Data > Data Validation (in Excel 2007) or Data > Validation (in Excel 2003) and the Data Validation window will appear as shown below:

Data Validation of Cell

Select List from the Allow drop-down list and in the Source text box enter “=Services” as shown above. Repeat the exercise for the other cells on the Main worksheet (in my case D2 “=Categories” and F2 “=Severities”) and your good to go. All you now need to do is copy row 2 to the lower rows.

Hope this helps. As always, feedback is always welcome (both positive and negative).