Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
HI ALL , and a good day
I have a very big need ! I have this combined 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) what I need is a function to be equivalent (!) to this (!) function posted here any sugestion will be more then wellcome |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
How about
=COUNTIF(B1:F1,"<=1")+COUNTIF(B3:F3,"<=1")+COUNTIF (B5:F5,"<=1") Hope this helps, Hutch "ytayta555" wrote: HI ALL , and a good day I have a very big need ! I have this combined 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) what I need is a function to be equivalent (!) to this (!) function posted here any sugestion will be more then wellcome |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
Does this work for you:
=COUNT(B1:F1;B3:F3;B5:F5)<=1 ? -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "ytayta555" wrote in message ... HI ALL , and a good day I have a very big need ! I have this combined 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) what I need is a function to be equivalent (!) to this (!) function posted here any sugestion will be more then wellcome |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
On 6 Mai, 17:30, Tom Hutchins
wrote: How about =COUNTIF(B1:F1,"<=1")+COUNTIF(B3:F3,"<=1")+COUNTIF (B5:F5,"<=1") Thanks very much , Tom It is indeed a variant for me ; please to help me with another variant ; I have get this formula : =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 =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)) Can you make ajustaments and match with this formula I get ,for my function posted here ? Thanks very much for your time |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
Thanks RagDyeR
Your variant doesn't work for me because it don't show me if every of 5 COUNT functions have the value <=1 ;in my first example , AND function show me if every of 5 Count functions have value = ,,<=1,, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
I'm not sure what you want...
To adapt the SUMPRODUCT formula to give the same results as our earlier multiple COUNTIF, use =SUMPRODUCT(ISNUMBER(MATCH(ROW(B1:F5), {1,3,5},0))*(B1:F5<=1)) Is that what you wanted? Hutch "ytayta555" wrote: On 6 Mai, 17:30, Tom Hutchins wrote: How about =COUNTIF(B1:F1,"<=1")+COUNTIF(B3:F3,"<=1")+COUNTIF (B5:F5,"<=1") Thanks very much , Tom It is indeed a variant for me ; please to help me with another variant ; I have get this formula : =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 =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)) Can you make ajustaments and match with this formula I get ,for my function posted here ? Thanks very much for your time |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Equivalent need
Thanks very much , mister Tom Hutchins
This help me really ; I understand now that I must do my homework with this four functions (SUMPRODUCT, ISNUMBER,MATCH,ROW), to get finally what I need ; excuse me for late feedback . I'll post today or tomorrow what I try to do , because of miss of time now ; I really got a new way for me ; hope to visite me here after I'll post with details what I try to do ; I think shall be something very usefull for a lot of excel users , I find after some few months how to make 65536 Count functions with the arguments(references) in combinatoric order , in a few minutes ; such as : COUNT(B1;B2;B3;B4) COUNT(B1;B2;B3;B5) COUNT(B1;B2;B3;B6) COUNT(B1;B2;B3;B7) COUNT(B1;B2;B4;B5) etc..etc...to 65536 functions..... How many time take anybody to make 65536 functions like this ??? But , if you need (like me) to have 15 arguments/ references in a only Count function , how long time take ??? I-ll explain what solution I have found , and what I need actually . Thanks for your time |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NZ function equivalent in Excel | Excel Worksheet Functions | |||
equivalent for function , please for help | Excel Worksheet Functions | |||
Looking for the equivalent of a Maxif function | Excel Discussion (Misc queries) | |||
WORKDAY() Function Equivalent with SUMPRODUCT() | Excel Worksheet Functions | |||
What is the Excel equivalent of the CELL function? | Excel Worksheet Functions |