Cleaning Up COUNTIF

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

Phil Hageman

Is there a cleaner way to state this formula?
=COUNTIF(D5:D22,"<"&D2*0.9)-COUNTIF(ED:D22,"<"&D2*0.8)
 
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((D5:D22>=D2*0.8)*(D5:D22<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
 
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
 
Back
Top