Convert the multi-step sum of booleans 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)))}.)

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.