Friday, September 14, 2018

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

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?

No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...