Defining Ranges

S

Steve

I need to use non consequetive cells for a "=COUNTIF" formula.... for
example, in cell Z6.... I want to "countif" the following cells C6, F6,
I6..... How do I do that?

Thanks,

Steve
 
P

Peo Sjoblom

What do yoiu want to count? Numbers or textstrings?
If numbers there is a solution

=SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT(B1:B4,D1:E4,G1:G4))))>5))

will count cells greater than 5, you can also name the non continguous cells
and use

=SUMPRODUCT(--(LARGE(MyRange,ROW(INDIRECT("1:"&COUNT(MyRange))))>5))



Regards,

Peo Sjoblom
 
S

Steve

Peo,

I want to count "y's" in these cells (I want to assign a value of 1 to a "Y"
response). So in the cells I am having people fill in either Y or N and I
want to be able to convert the Y to a 1 and add it up. The problem is that
the cells I am tabulating are non continguous....

Thanks for your input,

Steve
 
A

Ashish Mathur

Hi,

You may want to use the DCOUNTA function. Please refer to the help menu for
this function. It is well explained there

Regards,
 
S

Steve

Thank you

Ashish Mathur said:
Hi,

You may want to use the DCOUNTA function. Please refer to the help menu for
this function. It is well explained there

Regards,
Click to expand...
 
A

Aladin Akyurek

Steve said:
I need to use non consequetive cells for a "=COUNTIF" formula.... for
example, in cell Z6.... I want to "countif" the following cells C6, F6,
I6..... How do I do that?

Thanks,

Steve
Click to expand...

If it involves every third in say B6:Y6...

Z6:

=SUMPRODUCT(--(MOD(COLUMN(B6:Y6)-ROW(B6)+0,3)=0),--(B6:Y6="y"))

would count "y" in B6:Y6 in the relevant cells.
 
Top