Count cells based on value in a range
 

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(J27:P32,$S$47,FALSE) + COUNTBYCOLOR(J27:P32,$T$47,FALSE)

Any thoughts?


--
bimmerman
------------------------------------------------------------------------
bimmerman's Profile: http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread...hreadid=570126

Count cells based on value in a range
 

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(J27:P32,$S$47:$T$47,FALSE)

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

You *MIGHT* get this to work:

=SUMPRODUCT(COUNTBYCOLOR(J27:P32,$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

"bimmerman" wrote
in message ...

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(J27:P32,$S$47,FALSE) + COUNTBYCOLOR(J27:P32,$T$47,FALSE)

Any thoughts?


--
bimmerman
------------------------------------------------------------------------
bimmerman's Profile:
http://www.excelforum.com/member.php...fo&userid=2623
View this thread: http://www.excelforum.com/showthread...hreadid=570126