# If formulas with multiple cells???

=?Utf-8?B?U2NodWx6eQ==?=
Guest
Posts: n/a

 7th Oct 2007
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

T. Valko
Guest
Posts: n/a

 7th Oct 2007
Try this:

=IF(COUNTIF(A1:E1,">=1")>=2,"Y","")

--
Biff
Microsoft Excel MVP

"Schulzy" <(E-Mail Removed)> wrote in message
news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
> 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

MartinW
Guest
Posts: n/a

 7th Oct 2007
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" <(E-Mail Removed)> wrote in message
news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
> 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

=?Utf-8?B?U2NodWx6eQ==?=
Guest
Posts: n/a

 7th Oct 2007
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" <(E-Mail Removed)> wrote in message
> news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
> > 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

>
>
>

Bob Phillips
Guest
Posts: n/a

 7th Oct 2007
=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" <(E-Mail Removed)> wrote in message
news:33E5B4D9-8674-4C80-9724-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
>> > 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

>>
>>
>>

MartinW
Guest
Posts: n/a

 7th Oct 2007
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" <(E-Mail Removed)> wrote in message
news:33E5B4D9-8674-4C80-9724-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
>> > 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

>>
>>
>>

=?Utf-8?B?U2NodWx6eQ==?=
Guest
Posts: n/a

 7th Oct 2007
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" <(E-Mail Removed)> wrote in message
> news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
> > 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

>
>
>

Bob Phillips
Guest
Posts: n/a

 7th Oct 2007
See my earlier response

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Schulzy" <(E-Mail Removed)> wrote in message
news:7188814B-D312-4DB5-8A27-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:89A6C83A-3D82-408D-864A-(E-Mail Removed)...
>> > 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

>>
>>
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post LubberLou Microsoft Excel Worksheet Functions 6 10th Sep 2008 10:12 PM Zakhary Microsoft Excel Worksheet Functions 1 2nd May 2008 12:08 AM =?Utf-8?B?YmVsbHNqcmI=?= Microsoft Excel Worksheet Functions 0 14th Jul 2006 10:01 AM Microsoft Excel Misc 0 29th Jul 2004 04:43 PM bob green Microsoft Excel Worksheet Functions 3 21st Sep 2003 06:53 PM

Features