# Convert the multi-step sum of booleans into a single formula

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`:

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

Question
How do I convert this into a single formula?

The function you are after is `COUNTIF()`:

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

``````=COUNTIF(A3:A8,">"&D3)
``````

`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

``````=SUMPRODUCT(--(A3:A8>D3))
``````

or an array entered one like

``````{=SUM(--(A3:A8>D3))}
``````

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:

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

``````=COUNTIF(A3:A8,">"&AVERAGE(A3:A8))
``````

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

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

``````=SUMPRODUCT(--(A102:A107>D102:D107))
``````