Prev: Insert Copied Rows or Drag/Drop Formulas While Protected
Next: Nested IF Function not working
From: ytayta555 on 3 Jul 2008 14:35
HI eveybody

I have this formula :


=AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D1;D3;D5)<=1;COUNT(E1;E3;E­
5)<=1;COUNT(F1;F3;F5)<=1)

WHAT I NEED IS A PERFECT EQUIVALENT FOR THIS FORMULA !


This function let me to know if every one of the 5 COUNT functions
have the result the value <=1;
(this is what I need !!, not to count everyone of the five count
functions the value only <=1!!!; it must work like Count functions,
not like a Countif function {Countif <=1 }) ;

I get some variants , but them doesn't work ;(but I think the
solution
is somewhere here arround) :


=COUNT(INDEX(MATCH(ROW(B4:AH83),
{4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/
ISNUMBER(B4:AH83),0))
or


=SUMPRODUCT(ISNUMBER(MATCH(ROW(B4:AH83),
{4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0))*
ISNUMBER(B4:AH83))
or


=SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1))

LAST VARIANT I GET IS :

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)
but , it doesn't work , too ...

I tried this formula :
=AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})>=1)) , but when you work to
formula excel show you
that the range in formula is B1:D5 , not like in my first formula
B1;3;5 ; C1;3;5 ; D1;3;5; E1; .......etc .

I have in workbook in cell B1 value = 1 , C3 =1 , D5= 1000 (for
eg.) .When I add in cell D1 value = 5 , my
function become FALSE (what it is right) , and this formula remane
still TRUE (what it is wrong ).


I think the solution is somewhere arround , in this steps :


AND function first , then COUNT , then the combination of ROW and
MATCH functions ,
or another functions which give you the references identically for
the 5 Count functions from first formula ;
really , I don't understand what can do there ISNUMBER function , but
I don't know ;


Can anybody please to work for this function , please very much , I
tried in hundred weys , but
it still don't work .


Thank you very much for your time
From: TomPl on 3 Jul 2008 17:25
I think you might be looking for:
=count(C1:F5)

"ytayta555" wrote:

> HI eveybody
>
> I have this formula :
>
>
> =AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D1;D3;D5)<=1;COUNT(E1;E3;E­
> 5)<=1;COUNT(F1;F3;F5)<=1)
>
> WHAT I NEED IS A PERFECT EQUIVALENT FOR THIS FORMULA !
>
>
> This function let me to know if every one of the 5 COUNT functions
> have the result the value <=1;
> (this is what I need !!, not to count everyone of the five count
> functions the value only <=1!!!; it must work like Count functions,
> not like a Countif function {Countif <=1 }) ;
>
> I get some variants , but them doesn't work ;(but I think the
> solution
> is somewhere here arround) :
>
>
> =COUNT(INDEX(MATCH(ROW(B4:AH83),
> {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0)/
> ISNUMBER(B4:AH83),0))
> or
>
>
> =SUMPRODUCT(ISNUMBER(MATCH(ROW(B4:AH83),
> {4,7,9,11,14,16,18,22,25,35,46,57,68,72,83},0))*
> ISNUMBER(B4:AH83))
> or
>
>
> =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1))
>
> LAST VARIANT I GET IS :
>
> =AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1;0;1;0;1})<=1)
> but , it doesn't work , too ...
>
> I tried this formula :
> =AND(COUNT(MATCH(ROWS(B1:F5);{1;3;5})>=1)) , but when you work to
> formula excel show you
> that the range in formula is B1:D5 , not like in my first formula
> B1;3;5 ; C1;3;5 ; D1;3;5; E1; .......etc .
>
> I have in workbook in cell B1 value = 1 , C3 =1 , D5= 1000 (for
> eg.) .When I add in cell D1 value = 5 , my
> function become FALSE (what it is right) , and this formula remane
> still TRUE (what it is wrong ).
>
>
> I think the solution is somewhere arround , in this steps :
>
>
> AND function first , then COUNT , then the combination of ROW and
> MATCH functions ,
> or another functions which give you the references identically for
> the 5 Count functions from first formula ;
> really , I don't understand what can do there ISNUMBER function , but
> I don't know ;
>
>
> Can anybody please to work for this function , please very much , I
> tried in hundred weys , but
> it still don't work .
>
>
> Thank you very much for your time
>
 | 
Pages: 1
Prev: Insert Copied Rows or Drag/Drop Formulas While Protected
Next: Nested IF Function not working