Count cells based on value in a range

[bimmerman]

Hello all,

I am trying to count the number of cells based on a given value. The
problem is, every time I add a new value that I want added to my
calculation, I have to go back and add the new value to each
calculation cell.

Here's an example of a calculation I have. Just the $S$47 and $T$47 are
different. Now, the next time I add a new value, I'll have to add
another 'countbycolor' string to this calculation. Now what I'd like to
be able to do is have it just count based on a range of cells that I
give it which would contain all of my lookup values.

=COUNTBYCOLOR(J2732,$S$47,FALSE) + COUNTBYCOLOR(J2732,$T$47,FALSE)

Any thoughts?

 

[Biff]

Any thoughts?

COUNTBYCOLOR is not a standard function. So, you'd have to make sure the
code for that function will accept a criteria range. Something like this:

COUNTBYCOLOR(J2732,$S$47:$T$47,FALSE)

So, the "problem" is with the custom function.

You *MIGHT* get this to work:

=SUMPRODUCT(COUNTBYCOLOR(J2732,$S$47:$T$47,FALSE))

Those are my thoughts, now, do I have a solution? No! If the above doesn't
work you'd have to modify the code for the function. I can't help you with
that.

Biff