I have a few sensor readings and I'm checking to see if the values are in the acceptable range.

To do this I use an IF to check if the values are > or < the calculated (average) value. The results are stored in the respective columns. Finally I sum the results to get the count of how many are out of bounds (i.e. above the average).

For example, Ax is compared with Mean.Ax to get either 1 or 0 in If value is outside accepted bounds.Ax:

Image 1

Then the sum of If value is outside accepted bounds.Ax is performed to get Number of values outside bound.Ax:

Image 2, summation

How do I convert this into a single formula?


The function you are after is COUNTIF():

Worksheet Screenshot

Enter the following formula in G3 and ctrl-enter/copy-paste/fill-right into G3:I3:


COUNTIF() checks each value in the first argument against the criteria in the second one, and counts the number of times that it is met.

Using COUNTIF() is the simplest and best solution.

Of course, you could use a more complicated/harder to understand formula like


or an array entered one like


or even a more unnecessarily complicated version of those.

However, there is no benefit to be had by using any of those in this particular case.

If fact, since you seem to be interested in reducing the number of helper columns, an even better overall solution would be to dispense with the Mean helper columns as well:

Worksheet Screenshot

Enter the following formula in D3 and ctrl-enter/copy-paste/fill-right into D3:F3:


(And yes, this formula could also be made harder to understand for a beginner by converting it to =SUMPRODUCT(--(A3:A8>AVERAGE(A3:A8))) or {=SUM(--(A3:A8>AVERAGE(A3:A8)))}.)

  • @AFH Thanks for the edit (and the upvote ;-) ). I wonder how that happened? And more importantly, how I failed to notice it ¯\_(ツ)_/¯
    – robinCTS
    Jul 3 '18 at 12:41
  • Congrats. The system bots select highly rated posts to use as audits in the LQP review queue. This one was selected as a "low quality" post -- a badge of honor. :-)
    – fixer1234
    Jul 26 '18 at 19:23
  • @fixer1234 Oh, of course (-‸ლ) I give three alternative solutions for the Y-problem and then proceed to supply an X-problem solution. And on top of that I offer up an explanation of how the COUNTIF() function works. Clearly this answer is Low Quality! Next time I'll just stick to a single line, code only answer. (PS Just checked the timeline. Looks like you failed the audit - you selected Looks OK ;-) )
    – robinCTS
    Jul 27 '18 at 2:52

SUMPRODUCT Function can solve your problem also.

enter image description here

Write this formula in G102 & Fill it Right from G102 to I102:


N.B. Adjust the Cell address in the formula according to your need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.