Count rows containing data
Table of Contents
1. 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:
Recommended articles
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.
Get Excel *.xlsx file
2. 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.
3. Count cells between two values
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 and H is in B9, cells B4, B5, B6, B7 and B8 are in between.
Explaining formula in cell E16
Step 1 - Find value in cell D13 in cell range B2:B11
The MATCH function finds the relative position of a value in an array or cell range.
MATCH(D13, B2:B11, 0)
becomes
MATCH("B", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)
and returns 2.
Step 2 - Find value in cell D14 in cell range B2:B11
MATCH(D14, B2:B11, 0)
becomes
MATCH("H", {"A"; "B"; "V"; "X"; "C"; "T"; "N"; "H"; "A"; "C"}, 0)
and returns 8.
Step 3 - Subtract positions
MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0)
becomes
2-8 equals -6.
Step 4 - Remove sign
We don't know where the values are in the cell range so it may happen that we get a negative number from time to time, this example is such occasion. The ABS function removes the sign from a number.
ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))
becomes
ABS(-6)
and returns 6.
Step 5 - Subtract with 1
The calculation counts the last cell as well, we only need the cells in between.
ABS(MATCH(D13, B2:B11, 0)-MATCH(D14, B2:B11, 0))-1
becomes
6-1
and returns 5 in cell E16.
Get Excel *.xlsx file
formula to count cells between two values.xlsx
4. Count cells based on a condition and month
In A column, there are dates in mmddyyy format and in B column, there are two variables uses either "PASS" or "FIAIL". All I want to do is to count the "PASS" in individual month range.
Can someone help me in it. I am able to count days of month from the column A but can not link it with Column B.
Answer:
Formula in cell C1:
Copy cell C1 and paste down as far as needed.
Explaining formula in cell C1
Step 1 - Create arrays
The YEAR function returns the year from an Excel date.
=SUMPRODUCT(--(YEAR(A1)=YEAR($A$1:$A$30)), --(MONTH(A1)=MONTH($A$1:$A$30)), --(B1=$B$1:$B$30))
contains three criteria. The first criterion: --(YEAR(A1)=YEAR($A$1:$A$30))
becomes
--(2010=2010, Â 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010)
becomes
--(TRUE, Â TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)
and creates this array:Â (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
The MONTH function returns a number from 1 to 12 representing the month. Jan = 1, Feb = 2 ... Dec = 12.
The second criterion: --(MONTH(A1)=MONTH($A$1:$A$30))
creates this array:Â (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)
The third criterion: --(B1=$B$1:$B$30)
creates this array: (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1)
Step 2 - The product of three arrays
The SUMPRODUCT function multiplies the arrays (product) and then add the numbers in the array and returns a total (sum).
The entire formula now becomes
=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), (1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0), (1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))
and then
=SUMPRODUCT((1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)*(1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0, 1, 0, 0)*(1, 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 1, 0, 1))
becomes
Step 3 - Sum array
=SUMPRODUCT(1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0) equals 7.
Picture of formula calculations in cell C5
In order to show headers I had to adjust formula ranges:
Count category
Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]
Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
Excel categories
4 Responses to “Count rows containing 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.
Here is another (slightly shorter) way to write the formula that gets put into C1...
=SUMPRODUCT((TEXT($A$1:$A$30,"mmyyyy")=TEXT(A1,"mmyyyy"))*($B$1:$B$30=B1))
Great work but this is not exactly what I require, I think I was unable to convey my message correctly.
Well, here is it again. Consider the first excel representation with 3 columns A=Date, B=FAIL/PASS & C=count. I don't actually want C with A & B.
In sheet 1, only A & B exist and in sheet 2, it shows like A=Months(January,feb etc) & B=2. Simply it means 2 students pass during January 2010. In sheet 2, there are only 12 cells in A, which are names of months and infront of them, the count of PASS is calculated.
I hope, I have conveyed my message now.
@Janib,
For the count of the passes by month, put this formula next to the January entry on Sheet2 and copy it down...
=SUMPRODUCT((MONTH(Sheet1!A$1:A$1000)=ROW(A1))*(Sheet1!B$1:B$1000="PASS"))
As structured, the formula will handle 1000 data entries on Sheet1... if you need more, just change both 1000's to whatever value you actually might need. If you also want a count of the fails, use the identical formula but change the word "PASS" to "FAIL"
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