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.

Advertisements

3 thoughts on “How to count the number of occurrences of something in an Excel column

  1. What if some cells contain multiple “X’s”, i.e. xx or xxxx. I’ve having trouble finding a way to count my total number of “X’s”, apparently because I allow a single cell to have multiple “X’s” in them (I’m counting occurences of an event in a cell). Hope you have a suggestion. Thanks

  2. I want to ask..what if the ‘X’ is a colour? Say for instance, on a vacation calendar and its colour coded and I want it to count sick, personal and vacation days. Is that possible?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s