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.
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.
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
The example worksheet with all the calculations is available here.