Ask Your Question
0

COUNTIF used with function in criteria

asked 2016-03-30 16:11:19 +0200

jesse_m gravatar image

updated 2016-03-30 16:12:13 +0200

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-03-30 17:40:21 +0200

karolus gravatar image

Hallo

=SUMPRODUCT(C2:C10>=0.7*MAX(C2:C10))
edit flag offensive delete link more

Comments

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

jesse_m gravatar imagejesse_m ( 2016-03-30 19:52:18 +0200 )edit

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.)

Lupp gravatar imageLupp ( 2016-03-31 16:15:23 +0200 )edit
0

answered 2016-03-31 03:21:32 +0200

LKeithJordan gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-03-30 16:11:19 +0200

Seen: 142 times

Last updated: Mar 31 '16