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.
No comments:
Post a Comment