Hi, I have a very large set of data (several thousand rows, 150 columns), and I'm trying to come up with a way to count the number of times each row meets a specific criteria.
In essence, each cell has a number in it, and then there's a total row at the top that sums all the numbers from each column. I've used conditional formatting to highlight each cell in which the number in the cell is at least 0.1% of the total for the column. What I want is, at the right side of the table, another column that displays how many times each row met this condition. Since countif doesn't appear to work with array formulas, a formula like the below doesn't work:
=COUNTIF($B3:$EM3/$B$1:$EM$1,">=.001")
Because of the sheer number of columns, I'm really, really hoping to avoid adding 140+ helper columns. Is this something that can be done with SUMPRODUCT, and if so, what would that look like? If not, what would be the best way to go about this?
Thanks,
Jeff
