Count rows with data
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data.
Formula in cell B17:
If your data set, for example, has 5 columns change:
- B3:D14 to your cell range
- the array from {1;1;1} to {1;1;1;1;1}, there must be as many 1's as there a columns in your data set.
- also change <3 to <5
Example, your cell range is A3:G14. The formula becomes:
Explaining formula in cell B17
Step 1 - Check if cell is empty
The equal sign allows you to compare each cell in B3:D14 with an empty value "".
B3:D14="" returns an array of boolean values indicating if a cell is empty or not. {FALSE, FALSE, FALSE; ... }
The picture above shows the array to the right and the corresponding values to the left.
Step 2 - Convert boolean values to numbers
To convert the boolean array to 1 and 0 (zero) I multiply with 1. The parentheses allow you to determine the order of operation.
I want to compare the values with "" before I mutlitply with 1.
(B3:D14="")*1 returns {0, 0, 0; ...)
The picture above shows the array to the right.
Step 3 - Add values row-wise
The MMULT function is great for adding values row by row, however, it can not handle boolean values. The function returns an array of values.
MMULT((B3:D14="")*1,{1;1;1})
There are two arguments in the MMULT function, array1 and array2.
The picture above shows you the result from the MMULT function in the blue rectangle.
To learn more about the MMULT function read this:
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]
Step 4 - Check if each value in the array is smaller than 3.
If there are three empty values in a row that row is empty. That is why I check if each row is less than 3 indicating that at least one cell is not empty.
MMULT((B3:D14="")*1,{1;1;1})<3 returns {TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}
The array is shown to the right in the picture above.
Step 5 - Count rows
To be able to sum the array of boolean values I have to multiply with 1 to convert them to 1 or 0 (zero). TRUE = 1 and FALSE = 0.
SUMPRODUCT((MMULT((A3:G14="")*1,{1;1;1;1;1;1;1})<7)*1)
becomes
SUMPRODUCT({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}*1)
becomes
SUMPRODUCT({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}*1)
becomes
SUMPRODUCT({1; 0; 1; 1; 0; 1; 1; 1; 0; 1; 1; 1}) and returns 9 in cell B17.
Why not use the SUM function? Then you would have to enter the formula as an array formula.
Download Excel *.xlsx file
Count complete rows
The following formula in cell B17 counts complete rows, in other words, all cells in a row must be non-empty.
See formula in picture above.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count specific text string in a cell
Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
One Response to “Count rows with data”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Can you advise how to do the same in VBA, with use of dynamic arrays?
This is my attemt, but it returnes an error:
Dim count4deg As Integer Dim tempArr As Variant ReDim tempArr(1 To tNumber) For i = 1 To tNumber tempArr(i) = i Next i With WorksheetFunction count4deg = .SumProduct((.MMult((Stress4deg = "") * 1, tempArr) = 0) * 1) End WithStress4deg is an 2D array with numeric values
tNumber is the number of columns in the Stress4deg array
Regards
Bartek