A good day !

I have this function :


=AND(COUNT(B1;B3;B5)<=1;COUNT(C1;C3;C3)<=1;COUNT(D 1;D3;D5)<=1;COUNT(E1;E3;E5)<=1;COUNT(F1;F3;F5)<=1) ;

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 }) ;

The last result of this this function I want to show me if all 5
Count functions result is <=1 ;

This function work perfect for me , but I need very very strong a
perfect equivalent for this function (why I need this , it's a long
story ...) ,made with another functions .


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))



Can you help me to get a function which do the same thing like this
function ?

Thanks very much for your time .

You STILL have not explained why your first formula is not suitable
for you - you said it works perfectly for you.

Pete

On Jul 2, 4:05*pm, ytayta555 wrote:
A good day !

I have this function *:

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

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 }) ;

The last result of this this function I want to show me if all *5
Count functions result is *<=1 ;

This function work perfect for me , but I need very very strong a
perfect equivalent for this function (why I need this , it's a long
story ...) ,made with another functions .

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))

Can you help me to get a function which do the same thing like this
function ?

Thanks very much for your time .

On 2 Iul, 19:11, Pete_UK wrote:
You STILL have not explained why your first formula is not suitable
for you - you said it works perfectly for you.

Thanks , mr. Pete

I'ts a long long story ! ...
Imagine that you need to make / built milions of this kind of
functions ! milions of functions ...
imagine this thing ! the references in this functions will be in
combinatoric order ;
I use an VBA macro to built this kind of functions (I found the
resolve after many
months of search .....)
if I'll find another equivalent for this function I'm shure Ill can
built them more easy

ytayta555 wrote...
I have this function *:

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

This is a formula involving many functions, not one function.

this function let me to know if every one of the 5 COUNT functions
have the result the value <=1;
...
The last result of this this function I want to show me if all *5
Count functions result is *<=1 ;

This function work perfect for me , but I need very very strong a
perfect equivalent for this function . . .
...

So you have a range like B1:F5 from which you want to count numbers in
each column separately for rows 1, 3 and 5 only. You could try array
formulas like

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1; 0;1;0;1})<=1)

[NOTE: US standard syntax - comma (,) as argument separator, semicolon
(;) as ROW separator in array constants. Change as needed for your
locale's syntax.]

On 2 Iul, 21:15, Harlan Grove wrote:

Thanks very much , I'll work and I'll comunicate later what result I
get ;
please very much to watch the topic in future , you gived me a wey
very
important for me .

I don't understand why function ISNUMBER must to be use , I think is a
mistake ;
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
this 5 Count functions ;
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

Sorry , I forget to say I use excel 2003 ,
Romania .

ANY IDEEA HERE , PLEASE ? ! ...

ytayta555 wrote...
I don't understand why function ISNUMBER must to be use , I think
is a mistake ;

No. You're mistaken. The formula I proposed,

=AND(MMULT(TRANSPOSE(ROW(rng))^0,ISNUMBER(rng)*{1; 0;1;0;1})<=1)

*sums* rather than counts. It sums 1's in the separate columns of rng
when cells in rng contain numbers, and 0's when cells in rng don't
contain numbers. There's no way to make this MMULT approach work
without using ISNUMBER.

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 was clear that I was using US formula syntax, which means my formula
used commas (,) rather than semicolon (;) as argument separator, and
semicolons (;) rather than something else as row separators in array
constants. I don't recall what the corresponding character is for
European locales which use semicolons as argument separators, but you
could find out by typing BUT NOT ENTERING the formula =ROW(1:10) and
pressing [F9] - the character separating the numbers would be your
locale's array constant row separator.

The reason the formula above doesn't work for you is that in your
locale's formula syntax, the array constant {1;3;5} is the equivalent
of {1,3,5} in US locale formula syntax. I mean {1;3;5} in US locale
formula syntax, and IT'S UP TO **YOU** TO TRANSLATE THIS INTO YOUR OWN
LOCALE'S FORMULA SYNTAX.

If you can't figure out your own locale's formula syntax, change your
formula above to

=AND(COUNT(MATCH(ROWS(B1:F5);TRANSPOSE({1;3;5}))= 1))

WAW

Thank you very very much mr. Harlan Grove
Now I am working and I'll post my results in this night or
tomorrow .
Thank you so much , this formula is very very important for me .