How to Count Columns Containing Data in an Excel Range
Question details
The user needs to calculate the total number of columns within specific ranges that contain at least one non-blank cell, rather than counting every individual populated cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating metrics such as 'Days Worked' across multiple disconnected ranges (e.g., F2:K15 and F17:K32) where each column represents a day and any data entered in that column means the day should be counted.
- Observed behavior
- Standard counting functions like COUNT or COUNTA return the total number of non-empty cells. The user requires a method to evaluate whole columns and return a count of populated columns (e.g., returning 6 if six columns contain data).
Identify the exact cell ranges you need to evaluate (such as F2:K15) and decide whether you prefer using a built-in array formula or creating a custom VBA macro.
Use an Array Formula with SUMPRODUCT and MMULT
This method uses built-in functions to evaluate the range without needing to enable macros, making it ideal for standard spreadsheet sharing.
Combining SUMPRODUCT and MMULT allows Excel to perform matrix multiplication. This evaluates which columns in a two-dimensional range contain at least one non-blank cell and sums the resulting true/false array.
Click on the cell where you want the final column count (e.g., your 'Days Worked' total) to appear.
Type the formula combining SUMPRODUCT, MMULT, and TRANSPOSE targeted at your specific range. For example, evaluating F2:K15 checking for non-blank cells.
Press Enter. In some older versions of Excel, you may need to press Ctrl + Shift + Enter to properly execute it as an array formula.
Create a Custom VBA Function (ColCount)
Creating a User Defined Function (UDF) simplifies the formula in your worksheet, making it highly readable and easy to apply across multiple ranges.
Calculate Complex Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas and VBA macros, empowering you to seamlessly count columns containing data just as you would in Microsoft Excel.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the data ranges.
- 2. Select your output cell: Click on the cell where you want to display the total number of columns containing data.
- 3. Enter the formula: Type in your SUMPRODUCT/MMULT array formula or your custom =ColCount() function.
- 4. Get instant results: Press Enter to execute the calculation and view your accurately counted columns instantly.
Frequently Asked Questions
Can I use COUNTIF to count columns with data?
A standard COUNTIF function evaluates individual cells based on a condition, not entire columns at once. However, it can be nested inside an array formula (like SUMPRODUCT) to help evaluate matrix data across columns.
Why does my custom ColCount function return a #NAME? error?
The #NAME? error usually occurs if macros are disabled in your security settings, if the module containing the VBA code wasn't saved correctly, or if there is a typo in the function name when typed into the cell.
How do I modify this to count completely empty columns instead?
To count empty columns, you can either subtract the populated column count from the total number of columns in your range (e.g., using COLUMNS(F2:K15)), or adjust your array formula logic to count columns where the non-blank cell count equals zero.
