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?

Solution 1:

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

Solution 2:

SUMPRODUCT Function can solve your problem also.

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

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

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