Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
Ok so I cant fiure this one out Help please anyone....
I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
Try this:
=IF(COUNTIF(A1:E1,"=1")=2,"Y","") -- Biff Microsoft Excel MVP "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
Hi Schulzy,
Try this, =IF(COUNTIF(A1:E1,"=1")1,"Y","") If you mean more than one row, say 5 rows by 5 columns change it to =IF(COUNTIF(A1:E5,"=1")1,"Y","") HTH Martin "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
Thanks but my 5 cells are not in consequtive range eg A,D,G,J,M,O this
formula only allows for three cells not the full five "T. Valko" wrote: Try this: =IF(COUNTIF(A1:E1,"=1")=2,"Y","") -- Biff Microsoft Excel MVP "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
=IF(SUMPRODUCT(--(MOD(COLUMN(A2:M2),3)=1),--(A2:M2=2))=2,"Y","")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Schulzy" wrote in message ... Thanks but my 5 cells are not in consequtive range eg A,D,G,J,M,O this formula only allows for three cells not the full five "T. Valko" wrote: Try this: =IF(COUNTIF(A1:E1,"=1")=2,"Y","") -- Biff Microsoft Excel MVP "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
You're not making it very easy. First you say columns A to E,
then you say another five cells and list 6 cells..?? Anyway I'll take a guess and try this approach. Select A1 Hold down Ctrl and select D1, G1, J1, M1 and O1 Click in the Name Box and name your range Rang1 then use this formula in a cell of your choice. =IF(INDEX(FREQUENCY(Rang1,{0.99999999}),2)1,"Y"," ") HTH Martin "Schulzy" wrote in message ... Thanks but my 5 cells are not in consequtive range eg A,D,G,J,M,O this formula only allows for three cells not the full five "T. Valko" wrote: Try this: =IF(COUNTIF(A1:E1,"=1")=2,"Y","") -- Biff Microsoft Excel MVP "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
Thanks all
Sorry if I didnt explain all that well to start so what I wanted to do was set an If formula that would show Y if 2 out of five cells had a value of greater than 1 - the cells I have that this needs to relate to are K1,N1,Q1,T1,W1. I have found a resolution by inserting hidden columns that role into a countif formula and then loaded a standard IF formula from the hidden column If there is still an easier way to load this would love to hear it but thanks to everyone for suggestions "MartinW" wrote: Hi Schulzy, Try this, =IF(COUNTIF(A1:E1,"=1")1,"Y","") If you mean more than one row, say 5 rows by 5 columns change it to =IF(COUNTIF(A1:E5,"=1")1,"Y","") HTH Martin "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If formulas with multiple cells???
See my earlier response
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Schulzy" wrote in message ... Thanks all Sorry if I didnt explain all that well to start so what I wanted to do was set an If formula that would show Y if 2 out of five cells had a value of greater than 1 - the cells I have that this needs to relate to are K1,N1,Q1,T1,W1. I have found a resolution by inserting hidden columns that role into a countif formula and then loaded a standard IF formula from the hidden column If there is still an easier way to load this would love to hear it but thanks to everyone for suggestions "MartinW" wrote: Hi Schulzy, Try this, =IF(COUNTIF(A1:E1,"=1")1,"Y","") If you mean more than one row, say 5 rows by 5 columns change it to =IF(COUNTIF(A1:E5,"=1")1,"Y","") HTH Martin "Schulzy" wrote in message ... Ok so I cant fiure this one out Help please anyone.... I am trying to set a formula that states if any 2 or more of columns A, B, C, D and E are equal to or greater than 1 then formula cell will equal Y Can anyone tell me how to set this standard If formula doesnt work |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
"If, Then" Formulas for multiple cells | Excel Worksheet Functions | |||
Sort multiple columns with multiple formulas without returning #R | Excel Worksheet Functions | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
make multiple cells in 1 worksheet equal multiple cells in another | Excel Worksheet Functions |