Defining Ranges
 

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

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




"Steve" wrote:

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

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

"Peo Sjoblom" wrote:

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




"Steve" wrote:

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

Hi,

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

Regards,

"Steve" wrote:

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

Thank you

"Ashish Mathur" wrote:

Hi,

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

Regards,

"Steve" wrote:

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

Steve wrote:
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

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.