I am trying to count the number of cells in a column that fit a certain criteria. My criteria however contains other LO Calc functions. What I am trying is "=COUNTIF(C2:C10, ">=0.7*"MAX(C2:C10)). I want to count the number of cells that are greater than 70% of the maximum value in the column. I have tried sticking a & before the MAX function, moving the quotation marks around to include the multiplication symbol. No matter what I do I get errors in the 508 to 510 range. If anyone has any Ideas I would appreciate it.
Hallo
=SUMPRODUCT(C2:C10>=0.7*MAX(C2:C10))Thank you for your answer! I am trying to count the cells, whose contents are greater than 70% of the maximum. Does this do that? Would you mind explaining? I am not quite sure I follow how this achieves that. Thank you
The Boolean values a comparison like C2:C10>Something will return under array evaluation (an array of TRUEs and FALSEs) can also be passed to an arithmetic expression, then viewed as an array of 1 and 0 respectively. Using SUMPRODUCT instead of SUM makes sure that the parameters are evaluated in array mode. They are specified ‘ForceArray’. Finally a product consisting of only one factor is defined to evaluate to this single factor. (Same usage as is common in mathematics.)
This should do the trick:
=COUNTIF(C2:C10,">"&0.7*MAX(C2:C10))
Enjoy.
Please click the checkmark next to the response you believe best answers your question.