number of cells

Hi there;
1-What is the equation to find the number of cells in a range which their
values are more than 0.0?

2- How to count the number of cells in range which for every 3 consqueative
cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count
= 1 then next 3 cells, ... and if one 3 consequative cells have (even one
cell) a 0.0 value then that 3 cells donot add to our count number?

Appreciate any help.
Best
Darius

Is this a homework assingment.

look in help index for COUNTIF

--
Don Guillett
SalesAid Software
[email protected]
"Darius" <[email protected]> wrote in message
news:[email protected]...
> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3
consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

Hi Darius,

I'm a Newbie and Dummy here, but maybe this is (can be) a solution.

Lets say your numbers are in A1 - A50.
Use Colom B.
B1 = if(a1>0;1;0)
Copie this from B1 to B50

Then count (B1:B50). (Zigma)

Maybe....


Just Try it ................

actually, all 4 of them :-)

KL


"Darius" <[email protected]> wrote in message
news:[email protected]...
> Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> which of those "3" should be replaced by 5?
> Thanks a lot.
> Besr
> Darius
>
> "KL" wrote:
>
>> I assumed the number of cells in your range would be a multiple of 3
>> :-( Try
>> this:
>>
>> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>>
>> KL
>>
>>
>> "Darius" <[email protected]> wrote in message
>> news:[email protected]...
>> > thanks but the second equation (below)which I use for "E3:E94" and
>> > contains
>> > numbers, results in:
>> > #REF
>> > Any suggestion?
>> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>> >
>> > Best
>> > Darius
>> > "KL" wrote:
>> >
>> >> Hi Darius,
>> >>
>> >> For the first task use the following formula:
>> >>
>> >> =COUNTIF(A1:A10,">0")
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >> "Darius" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi there;
>> >> > 1-What is the equation to find the number of cells in a range which
>> >> > their
>> >> > values are more than 0.0?
>> >> >
>> >> > 2- How to count the number of cells in range which for every 3
>> >> > consqueative
>> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
>> >> > then
>> >> > count
>> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
>> >> > (even
>> >> > one
>> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >> >
>> >> > Appreciate any help.
>> >> > Best
>> >> > Darius
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Thank you so much, works excellent
Darius

"KL" wrote:

> actually, all 4 of them :-)
>
> KL
>
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> > which of those "3" should be replaced by 5?
> > Thanks a lot.
> > Besr
> > Darius
> >
> > "KL" wrote:
> >
> >> I assumed the number of cells in your range would be a multiple of 3
> >> :-( Try
> >> this:
> >>
> >> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
> >>
> >> KL
> >>
> >>
> >> "Darius" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > thanks but the second equation (below)which I use for "E3:E94" and
> >> > contains
> >> > numbers, results in:
> >> > #REF
> >> > Any suggestion?
> >> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >> >
> >> > Best
> >> > Darius
> >> > "KL" wrote:
> >> >
> >> >> Hi Darius,
> >> >>
> >> >> For the first task use the following formula:
> >> >>
> >> >> =COUNTIF(A1:A10,">0")
> >> >>
> >> >> Regards,
> >> >> KL
> >> >>
> >> >> "Darius" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Hi there;
> >> >> > 1-What is the equation to find the number of cells in a range which
> >> >> > their
> >> >> > values are more than 0.0?
> >> >> >
> >> >> > 2- How to count the number of cells in range which for every 3
> >> >> > consqueative
> >> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
> >> >> > then
> >> >> > count
> >> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
> >> >> > (even
> >> >> > one
> >> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >> >
> >> >> > Appreciate any help.
> >> >> > Best
> >> >> > Darius
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
which of those "3" should be replaced by 5?
Thanks a lot.
Besr
Darius

"KL" wrote:

> I assumed the number of cells in your range would be a multiple of 3 :-( Try
> this:
>
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>
> KL
>
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > thanks but the second equation (below)which I use for "E3:E94" and
> > contains
> > numbers, results in:
> > #REF
> > Any suggestion?
> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >
> > Best
> > Darius
> > "KL" wrote:
> >
> >> Hi Darius,
> >>
> >> For the first task use the following formula:
> >>
> >> =COUNTIF(A1:A10,">0")
> >>
> >> Regards,
> >> KL
> >>
> >> "Darius" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi there;
> >> > 1-What is the equation to find the number of cells in a range which
> >> > their
> >> > values are more than 0.0?
> >> >
> >> > 2- How to count the number of cells in range which for every 3
> >> > consqueative
> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> >> > count
> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
> >> > one
> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >
> >> > Appreciate any help.
> >> > Best
> >> > Darius
> >> >
> >>
> >>
> >>
>
>
>

I assumed the number of cells in your range would be a multiple of 3 :-( Try
this:

=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))

KL


"Darius" <[email protected]> wrote in message
news:[email protected]...
> thanks but the second equation (below)which I use for "E3:E94" and
> contains
> numbers, results in:
> #REF
> Any suggestion?
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>
> Best
> Darius
> "KL" wrote:
>
>> Hi Darius,
>>
>> For the first task use the following formula:
>>
>> =COUNTIF(A1:A10,">0")
>>
>> Regards,
>> KL
>>
>> "Darius" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi there;
>> > 1-What is the equation to find the number of cells in a range which
>> > their
>> > values are more than 0.0?
>> >
>> > 2- How to count the number of cells in range which for every 3
>> > consqueative
>> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> > count
>> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
>> > one
>> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >
>> > Appreciate any help.
>> > Best
>> > Darius
>> >
>>
>>
>>

thanks but the second equation (below)which I use for "E3:E94" and contains
numbers, results in:
#REF
Any suggestion?
=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))

Best
Darius
"KL" wrote:

> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there;
> > 1-What is the equation to find the number of cells in a range which their
> > values are more than 0.0?
> >
> > 2- How to count the number of cells in range which for every 3
> > consqueative
> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> > count
> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >
> > Appreciate any help.
> > Best
> > Darius
> >
>
>
>

Hi again,

For task 2 try the following:

=SUMPRODUCT(--(COUNTIF(OFFSET(A1:A10,(ROW(INDIRECT("1:"&ROWS(A1:A10)/3))-1)*3,,3),">0")=3))

Reagards,
KL


"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
>> Hi there;
>> 1-What is the equation to find the number of cells in a range which their
>> values are more than 0.0?
>>
>> 2- How to count the number of cells in range which for every 3
>> consqueative
>> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> count
>> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
>> cell) a 0.0 value then that 3 cells donot add to our count number?
>>
>> Appreciate any help.
>> Best
>> Darius
>>
>
>

Hi Darius,

For the first task use the following formula:

=COUNTIF(A1:A10,">0")

Regards,
KL

"Darius" <[email protected]> wrote in message
news:[email protected]...
> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3
> consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

I wish I was that much young, no it is not homework,
Skrol I am afraid you way is not that much efficient. hope I get some other
response
Best
Darius
"Darius" wrote:

> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3 consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

I wish I was that much young, no it is not homework,
Skrol I am afraid you way is not that much efficient. hope I get some other
response
Best
Darius
"Darius" wrote:

> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3 consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

Hi Darius,

For the first task use the following formula:

=COUNTIF(A1:A10,">0")

Regards,
KL

"Darius" <[email protected]> wrote in message
news:[email protected]...
> Hi there;
> 1-What is the equation to find the number of cells in a range which their
> values are more than 0.0?
>
> 2- How to count the number of cells in range which for every 3
> consqueative
> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> count
> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> cell) a 0.0 value then that 3 cells donot add to our count number?
>
> Appreciate any help.
> Best
> Darius
>

Hi again,

For task 2 try the following:

=SUMPRODUCT(--(COUNTIF(OFFSET(A1:A10,(ROW(INDIRECT("1:"&ROWS(A1:A10)/3))-1)*3,,3),">0")=3))

Reagards,
KL


"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
>> Hi there;
>> 1-What is the equation to find the number of cells in a range which their
>> values are more than 0.0?
>>
>> 2- How to count the number of cells in range which for every 3
>> consqueative
>> cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> count
>> = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
>> cell) a 0.0 value then that 3 cells donot add to our count number?
>>
>> Appreciate any help.
>> Best
>> Darius
>>
>
>

thanks but the second equation (below)which I use for "E3:E94" and contains
numbers, results in:
#REF
Any suggestion?
=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))

Best
Darius
"KL" wrote:

> Hi Darius,
>
> For the first task use the following formula:
>
> =COUNTIF(A1:A10,">0")
>
> Regards,
> KL
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there;
> > 1-What is the equation to find the number of cells in a range which their
> > values are more than 0.0?
> >
> > 2- How to count the number of cells in range which for every 3
> > consqueative
> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> > count
> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even one
> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >
> > Appreciate any help.
> > Best
> > Darius
> >
>
>
>

I assumed the number of cells in your range would be a multiple of 3 :-( Try
this:

=SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))

KL


"Darius" <[email protected]> wrote in message
news:[email protected]...
> thanks but the second equation (below)which I use for "E3:E94" and
> contains
> numbers, results in:
> #REF
> Any suggestion?
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>
> Best
> Darius
> "KL" wrote:
>
>> Hi Darius,
>>
>> For the first task use the following formula:
>>
>> =COUNTIF(A1:A10,">0")
>>
>> Regards,
>> KL
>>
>> "Darius" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi there;
>> > 1-What is the equation to find the number of cells in a range which
>> > their
>> > values are more than 0.0?
>> >
>> > 2- How to count the number of cells in range which for every 3
>> > consqueative
>> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
>> > count
>> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
>> > one
>> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >
>> > Appreciate any help.
>> > Best
>> > Darius
>> >
>>
>>
>>

Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
which of those "3" should be replaced by 5?
Thanks a lot.
Besr
Darius

"KL" wrote:

> I assumed the number of cells in your range would be a multiple of 3 :-( Try
> this:
>
> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>
> KL
>
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > thanks but the second equation (below)which I use for "E3:E94" and
> > contains
> > numbers, results in:
> > #REF
> > Any suggestion?
> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >
> > Best
> > Darius
> > "KL" wrote:
> >
> >> Hi Darius,
> >>
> >> For the first task use the following formula:
> >>
> >> =COUNTIF(A1:A10,">0")
> >>
> >> Regards,
> >> KL
> >>
> >> "Darius" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi there;
> >> > 1-What is the equation to find the number of cells in a range which
> >> > their
> >> > values are more than 0.0?
> >> >
> >> > 2- How to count the number of cells in range which for every 3
> >> > consqueative
> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then
> >> > count
> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have (even
> >> > one
> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >
> >> > Appreciate any help.
> >> > Best
> >> > Darius
> >> >
> >>
> >>
> >>
>
>
>

actually, all 4 of them :-)

KL


"Darius" <[email protected]> wrote in message
news:[email protected]...
> Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> which of those "3" should be replaced by 5?
> Thanks a lot.
> Besr
> Darius
>
> "KL" wrote:
>
>> I assumed the number of cells in your range would be a multiple of 3
>> :-( Try
>> this:
>>
>> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
>>
>> KL
>>
>>
>> "Darius" <[email protected]> wrote in message
>> news:[email protected]...
>> > thanks but the second equation (below)which I use for "E3:E94" and
>> > contains
>> > numbers, results in:
>> > #REF
>> > Any suggestion?
>> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
>> >
>> > Best
>> > Darius
>> > "KL" wrote:
>> >
>> >> Hi Darius,
>> >>
>> >> For the first task use the following formula:
>> >>
>> >> =COUNTIF(A1:A10,">0")
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >> "Darius" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi there;
>> >> > 1-What is the equation to find the number of cells in a range which
>> >> > their
>> >> > values are more than 0.0?
>> >> >
>> >> > 2- How to count the number of cells in range which for every 3
>> >> > consqueative
>> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
>> >> > then
>> >> > count
>> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
>> >> > (even
>> >> > one
>> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
>> >> >
>> >> > Appreciate any help.
>> >> > Best
>> >> > Darius
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Thank you so much, works excellent
Darius

"KL" wrote:

> actually, all 4 of them :-)
>
> KL
>
>
> "Darius" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
> > which of those "3" should be replaced by 5?
> > Thanks a lot.
> > Besr
> > Darius
> >
> > "KL" wrote:
> >
> >> I assumed the number of cells in your range would be a multiple of 3
> >> :-( Try
> >> this:
> >>
> >> =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS(E3:E94)/3)))-1)*3,,3),">0")=3))
> >>
> >> KL
> >>
> >>
> >> "Darius" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > thanks but the second equation (below)which I use for "E3:E94" and
> >> > contains
> >> > numbers, results in:
> >> > #REF
> >> > Any suggestion?
> >> > =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3:E94)/3))-1)*3,,3),">0")=3))
> >> >
> >> > Best
> >> > Darius
> >> > "KL" wrote:
> >> >
> >> >> Hi Darius,
> >> >>
> >> >> For the first task use the following formula:
> >> >>
> >> >> =COUNTIF(A1:A10,">0")
> >> >>
> >> >> Regards,
> >> >> KL
> >> >>
> >> >> "Darius" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Hi there;
> >> >> > 1-What is the equation to find the number of cells in a range which
> >> >> > their
> >> >> > values are more than 0.0?
> >> >> >
> >> >> > 2- How to count the number of cells in range which for every 3
> >> >> > consqueative
> >> >> > cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0,
> >> >> > then
> >> >> > count
> >> >> > = 1 then next 3 cells, ... and if one 3 consequative cells have
> >> >> > (even
> >> >> > one
> >> >> > cell) a 0.0 value then that 3 cells donot add to our count number?
> >> >> >
> >> >> > Appreciate any help.
> >> >> > Best
> >> >> > Darius
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>