Imagine a retail Company hires you to manage its warehouse.
- But the storehouse is full
- New items are coming.
- The company need to dispatch or reordered the biggest items to create room.
- During the first week, you receive a phone call, the Logistic Manager ask you a question: how many items are in stock and how many of them has a volume greater than 3 cubic meters
- You have the stock list ready but you are not ready to give a quick response
- You say…I will phone back with the information
- Your boss, answer…No, I want to take the decision right now, so I will wait on phone.
- You don’t have that kind of report at your hands, even worst, you get nervous
Making a formula in such circumstances is not easy, the pressure diminishes your thinking capacity. You can satisfy your Boss’ impatience for information with Excel COUNT Function combined with Filter, Sort and other Commands.
Here are some tips to count successfully…
1) Keep Counting Results Consistent
Use data validation; drop down lists, autocomplete, custom lists, VLOOKUP.
- Leading spaces and Trailing spaces
- Inconsistent use of straight ( ‘ or ” ) and curly ( ‘ or “) quotation marks
- Nonprinting characters
- Numbers stored as texts
2) Know the 12 Types of Data you Can Count
- Numbers. Use COUNT or COUNTA
- Texts. Use COUNTA.
- Blanks. Use COUNTBLANK.
- Dates. Use COUNT or COUNTA.
- Cells. Use COLUMNS and ROWS to get the number of columns and rows and get their product.
- Formulas. Select Formulas that contain number, text, logical or errors. How? Press F5, then Go to Special, finally choose Formulas. Use the status bar to count the selection.
- Errors. Select Formulas that contain errors. How? Press F5, then Go to Special, choose Formulas and then choose only Errors. Use the status bar to count the selection.
- You can count errors with IFERROR and COUNTIF or VBA
- Constants. Select cells that contain constants. How? Press F5, then Go to Special, finally choose Constants. Use the status bar to count the selection.
- Specific values. Use COUNTIF.
- Formats. Count cells based on fill or font Color. How? Use Filter and then count the resulting data (Excel 2007) or Find data with specific format with Find and Select command and then count with the status bar (Excel 2003)
- Images. You have two options: Select images and count them in the selection pane visually (Excel 2007) or count them with VBA.
- Values that meet certain condition. Apply a Custom Filter and then count the results with the Total Row (COUNT or COUNT NUMBER function).
3) Identify 8 Counting Functions in Excel
- COUNT (statistical functions)
- COUNTA (statistical functions)
- COUNTBLANK (statistical functions)
- COUNTIF (statistical functions)
- COUNTIFS (statistical functions)
- CUBESETCOUNT (Cube functions)
- DCOUNT (Database functions)
- DCOUNTA (Database functions)
4) Know the Fastest Way to Count
Select the data and right click on the Status Bar, then select Count or Numerical Count.
Excel 2003 users only have Count Function at the Status Bar.