Count rows that contain specific values

Count Rows that contain Specific Values  

The Excel SUM function gives the sum of values that are given as multiple arguments. MMULT function is used to return the matrix product of 2 arrays. The Excel TRANSPOSE function is capable of flipping the orientation of a provided array or range. COLUMN function is used to return the column number in Excel. Using the SUM, MMULT, TRANSPOSE and COLUMN function together, we can count rows that contain specific values in Excel. 

Formula

The generic syntax of this formula is:

= SUM ( -- (MMULT( -- (criteria), TRANSPOSE(COLUMN(data_range)))>0))

Explanation

The formula incorporating Excel SUM, MMULT, TRANSPOSE and COLUMN functions to count rows that contain specific values uses the following arguments:

criteria (required): This is the specific value that we are looking out for in the range.

data_range (required): This is the set of values (rows and/or columns) from which we have to find the specific values.

This works as follows:

  • First, the formula checks as per the logical criteria and an array are formed with TRUE and FALSE values which automatically get converted to 1’s and 0’s. This goes into the MMULT function.
  • COLUMN function then returns the column number in the format of an array. Consequently, TRANSPOSE changes the previous column-array to a row-array.
  • In the final array which is evaluated by SUM function, a 1 shows the case when the logical test returned a true value. The total count of all the rows is given by the final value returned by the SUM function.

Example

In this example, we have several random values. To count rows that contain the specific value, 61, we use the following formula;

{=SUM(--(MMULT(--(B4:D13=61),TRANSPOSE(COLUMN(B4:D13)))>0))}

This is an array formula and must be entered with Control + Shift + Enter

This works as follows:

  • First, the formula checks as per the logical criteria and an array is formed with TRUE and FALSE values which automatically get converted to 1’s and 0’s, {0,1,0;0,0,0;0,0,0;0,0,0;0,0,0;0,1,1;0,0,0;1,0,0;0,0,0;1,1,0}. This goes into the MMULT function as array1.
  • COLUMN function then returns the column number in the format of an array. Consequently, TRANSPOSE changes the previous column-array to a row-array.
  • In the final array which is evaluated by SUM function {1;0;0;0;0;1;0;1;0;1}, a 1 shows the case when the logical test returned a true value. The total count of all the rows is given by the final value returned by the SUM function which comes out to be 4.

Figure 1. Example of how to count rows that contain specific values in Excel

Notes

If you wish to check for text values instead of numbers in the cells, you can do so by altering the logic in the formula and use the ISNUMBER and SEARCH functions. For example to count rows that contain “orange”, you can use this formula;

=ISNUMBER(SEARCH( “orange”, data))

Where data is the set of values from which we need to find the specific text.    

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar