Cleaning Up COUNTIF

[Phil Hageman]

Is there a cleaner way to state this formula?
=COUNTIF(D522,"<"&D2*0.9)-COUNTIF(ED22,"<"&D2*0.8)

 

[Karen]

First off, I'm assuming you meant D5 instead of ED in the
second countif equation. If that's the case, then you can
use the following array equation:

{=SUM((D522>=D2*0.8)*(D522<D2*0.9))}

(Don't type the brackets {} - they indicate an array-
entered equation - just hold down control-shift when you
hit enter instead of just hitting enter).

Hope that helps,
Karen

 

[Bob Phillips]

Non-array version

=SUMPRODUCT((D522>=D2*0.8)*(D522<D2*0.9))

 

[Phil Hageman]

Thanks, Karen. What advantage does the array feature
provide?

 

[Karen]

By entering this as an array formula, you are "looping"
through the ranges, returning either a 1 or a 0 (if it's
1*0 [true for criterion 1/false for criterion 2] or 0*1
[false/true] or 0*0 [false/false], the answer is 0; only
if it's 1*1 [true/true] do you return a 1). The SUM
function adds up this array and returns a single number
indicating the number of times both criteria hold true.

There's nothing particularly "better" about this method
than yours. It's less text, and in essence only 1
equation (as opposed to 2 COUNTIFs). It's also (imo) a
bit more intuitive upon sight. Personal preference. It's
the only way I could think to "clean up" your equation.
Not sure what else you might be looking for...

Good luck,
Karen