Use Excel SUMPRODUCTS to count Rows with Data

Gary Eckstein Microsoft Office 1 , , , , ,

I recently had the need to count the number of rows in a Microsoft Excel 2007 workbook. Normally I’d be able to use the ROWS function that ‘Returns the number of rows in a reference or array’. ROWS wouldn’t meet my needs as I required to calculate the number of rows where any value besides NULL may appear in multiple columns. For example:

ABC
1Sydney
2CairnsMelbourneSydney
3
4Newcastle

The above table is a simple example however illustrates what I was attempting to achieve. I was trying to find out, using a formula as the data changed often, how many rows had a value other than NULL where multiple columns may contain data. From the table we can tell that the answer is that three rows contain data (row 3 only contains NULL values).

Excel Formulas that won’t work

Excel has many very useful formulas and functions. Here are some commonly used functions that unfortunately won’t assist with our row calculation needs:

If I used the formula =ROWS(A1:C4) I’d get the total number of rows (i.e. 4) irrespective of whether there is a value in the field or not.

If I used the COUNTA function (i.e. =COUNTA(A1:A4,B1:B4,C1:C4)) I get a count of the number of cells that contain a value other than NULL.

For our needs we are going to use a more flexible and powerful function called SUMPRODUCT.

SUMPRODUCT Counts Rows with Values in Various Columns

SUMPRODUCT is very versatile Excel function and, although it requires a bit more advanced Excel user knowledge, is worth knowing because of its problem solving abilities. We’ll only touch on SUMPRODUCT capabilities to meet our needs.

First, let’s define what we are trying to achieve; we are attempting to calculate the number of rows where any value appears in one or more columns.

We don’t know what the value entered in the cells may be therefore can’t use a formula to count rows where a specific value appears. We also can’t count all rows where a NULL value appears in all columns as the number of rows may vary.

Our solution therefore is to count the number of rows where all column values are not NULL. Here therefore is the formula:

=SUMPRODUCT(–(A1:A4&B1:B4&C1:C4<>{“”}))

(just amend and expand the formula to meet your row and column needs).

Good luck!