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.
JOATIT 