How do I count # of rows in an array ONLY IF the sum of numbers for each row is >0 in Excel?

Question

I've got a 150 column x 360 row array with random numbers (say A2 to ET361) in Excel.

How do I calculate for each column (i.e., from cell B1 to ET1) how many rows are greater than zero for the columns before it?

Criteria:

B1 needs to calculate # of cells (A2 to A361) that are >0.
C1 needs to calculate # of rows (A2:B2, A3:B3, ..., to A361:B361) where the sum of each row is >0.
D1 needs to calculate # of rows (A2:C3, ..., to A361:C361) where the sum of each row is >0.

I've tried using the COUNTIF formula, but it only returns the # of cells, not the # of rows.
I think I need a nested ROWS() and IF() formula? I also don't want to create another 150 x 360 matrix in order to deal with this problem as I want to save space in my Excel file.

I also do not want to use macros and VBA as they complicate my spreadsheet.

---

I have one added complexity to the whole equation, whereby the subtotal function doesn't work.

I need for each cell within the matrix to calculate the # of rows above it for which the sum of columns for each row is greater than zero. The solution by Barry won't work in this instance (i've tested it) as 'Subtotal' formula doesn't work for cells which have 'subtotal' formula.

Do we have any other alternatives?

Answer 1

While I haven't been able to think up a single formula solution (maybe someone else will!), I did come up with something that takes up a lot less spreadsheet real estate than another 150 x 360 matrix.

The basic idea is to compute the cumulative totals in each row for one column of data and then use that in a data table ("what-if analysis") to generate the counts for all of the columns.

Starting point is the column of computations for the rows in a single data column.

As shown in the screenshot below, I set up a worksheet with 10 columns of data.

Helper Column

To the right of the data, I've set up the helper column L.

Cell L1 contains the COUNTIF of the rows in that column that have a sum greater than zero.

For the row sums, instead of a simple sum of the columns in each row (again, just for column A) I use a sum of the range returned by the OFFSET function. This function has the form

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

Cell L3 has the first of the SUM(OFFSET(...)) expressions. It calculates the row sum for the range that is 0 rows down from cell A2 and 0 columns to right, with a height of 1 row and a width equal to the value in cell L2. In this case, L2 has the value of 1.

This formula is copied down through 360 rows, in each case computing the sum of a range 1 row high and with a width determined by the value in cell L2.

For exampe, if the value in L2 were changed to 2, then the formulas in the column would calculate the the row-wise sums of the values in columns A & B for each of the 360 rows. And cell L1 would show the number of rows in the range A2:B361 with a sum greater than 0.

Data Table

Excel's data table functionality makes it possible to quickly determine the impact on a calculation of varying the value of one (or two) of the inputs to that calculation. It is set up via the What-If Analysis button in the Data Tools section of the Data tab on the ribbon.

The accompanying picture shows the data table setup.

The data table will be created in the range R1:S10. At the top of the table, in cell S1 is the result cell for which the inputs will be varied. In this case, the result cell holds the formula =L1, which is just a reference to the COUNTIF formula at the top of the helper column L.

I pre-entered the "what-if" values in cells R2:R10. The values shown - 1, 2, ... , 9 - represent the widths of the ranges that the OFFSET will return. And the "column input cell" is the cell L1, the cell that determines the width of the rows that are summed in the helper column.

In a nutshell, we feed in the widths 1-9 (equivalent to columns "A", "A:B", "A:C", etc.) and the data table computes the number of rows that have sums greater than 0 for each of those column spans.

The last picture shows the final results. The data table has calculated the row counts for each column of the input data, i.e., the counts of the row-wise sums (of the previous columns) that are greater than 0. Those counts were returned in cells S2:S10 of the data table. I transferred the counts to the first row of the original data using the TRANSPOSE function.

The example worksheet with all the calculations is available here.

Answer 2

If I understand correctly what you're asking, you want the top row to show, for each column, the total number of individual cells with a value > 0 in all columns before it. Right?

If so, this is pretty simple using CountIf and using the $ sign to lock the reference.

In cell B1, put in =CountIf($A2:A361,">0"). Click and drag to the right. The $ sign locks the A so that it's always counting everything between column A and the current column. The formula will look like this as you drag it across:

• C1 =Countif($A2:B361,">0")
• D1: =Countif($A2:C361,">0")
• E1: =Countif($A2:D361,">0")
• etc...

CountIf can count over an entire range, you don't have to just select a single cell or formula at a time. So by using it this way, you can easily count every single cell to the left of the current column.

Answer 3

OFFSET function allows you to separate out individual rows within a range...and then you can sum each row with SUBTOTAL and count the rows >0 with SUMPRODUCT, so this formula in B1 copied across should do the job with no helper cells

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

That uses a technique similar to that described here [there's no filtering here but SUBTOTAL still needs to be used to sum each range generated by OFFSET]

That will give you the same results as chuff's solution