Counting Cells

[Tyler]

How can I tally values in different cells that do no fall
into a specific range. For example, I would like to tally
all the values in E2,I2, L2, and P2 which meets a certain
criteria such as containing the number "3". While these
numbers do fall into the range C2:R2, I don't want all the
values for the other cells which I am not interested in.
So, how can get excel to isolate the cells that I am
interested in and giving me a value?

 

[Tyler]

Hey,

That actually worked! I'm impressed!! My question now is
how can this formula be applied to all the other rows. For
example, I got the answer for E2, I2, N2, and P2. Now, how
do I get the answer for the remaining cells which go from
E3, I3, N3,P3 to E17, I17, N17, P17. Thanks for your help.

Tyler

 

[Frank Kabel]

Hi
not testedt but give this a try:
=SUMPRODUCT(--(LARGE(OFFSET(NCrange,ROW(1:1)-1,0),ROW(INDIRECT("1:"&COU
NT(NCrange))))=3))
and copy this formula down for your rows

 

[Peo Sjoblom]

Select all cells that need to be in this formula (select by holding down
ctrl and clicking each cell)
use the same defined name and the same formula

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom