check if any values on row
in [Functions]
|
From: Teethless mama on 28 Feb 2010 22:29 this one will do the trick =IF(COUNT(D1:CT1)-COUNTIF(D1:CT1,0),"Row with data","Empty row") "Norvascom" wrote: > On Feb 28, 8:02 pm, Teethless mama > <Teethlessm...(a)discussions.microsoft.com> wrote: > > Try this: > > > > =IF(SUM(D1:CT1),"Row with data","Empty row") > > > > > > > > "Norvascom" wrote: > > > On Feb 28, 4:01 pm, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > > > > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > > > > > <norvas...(a)gmail.com> wrote: > > > > >Hi, > > > > > > >I am trying to have a formula on cell A1 for example that would look > > > > >on cells D1:CT1 to see if there are any values different than 0 (in > > > > >other words not empty or equal to 0). > > > > >If there are any values different than 0, cell A1 would indicate "Row > > > > >with data" if none, it would say "Empty row". > > > > > > >Any help would be appreciated. > > > > >Thanks > > > > > > Try this formula: > > > > > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > > > > > Note: This is an array formula that should be confirmed with > > > > CTRL+SHIFT+ENTER rather than just ENTER. > > > > > > Hope this helps / Lars-Åke > > > > > Thanks it works, but would there be any formula that would calculate > > > automatically the result without having to press CTRL+SHIFT+ENTER. > > > .- Hide quoted text - > > > > - Show quoted text - > > > The sum formula won't work, because if for instance on one cell there > is a +10 and on another a -10 it will indicate "row with no data" > while there are values. Please let me know any other formula that > would work. > . >
From: Teethless mama on 28 Feb 2010 22:43 Another one... =IF(MAX(INDEX(ABS(D1:CT1),)),"Row with data","Empty row") "Teethless mama" wrote: > this one will do the trick > > =IF(COUNT(D1:CT1)-COUNTIF(D1:CT1,0),"Row with data","Empty row") > > > > "Norvascom" wrote: > > > On Feb 28, 8:02 pm, Teethless mama > > <Teethlessm...(a)discussions.microsoft.com> wrote: > > > Try this: > > > > > > =IF(SUM(D1:CT1),"Row with data","Empty row") > > > > > > > > > > > > "Norvascom" wrote: > > > > On Feb 28, 4:01 pm, Lars-Åke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > > > > > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > > > > > > > <norvas...(a)gmail.com> wrote: > > > > > >Hi, > > > > > > > > >I am trying to have a formula on cell A1 for example that would look > > > > > >on cells D1:CT1 to see if there are any values different than 0 (in > > > > > >other words not empty or equal to 0). > > > > > >If there are any values different than 0, cell A1 would indicate "Row > > > > > >with data" if none, it would say "Empty row". > > > > > > > > >Any help would be appreciated. > > > > > >Thanks > > > > > > > > Try this formula: > > > > > > > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > > > > > > > Note: This is an array formula that should be confirmed with > > > > > CTRL+SHIFT+ENTER rather than just ENTER. > > > > > > > > Hope this helps / Lars-Åke > > > > > > > Thanks it works, but would there be any formula that would calculate > > > > automatically the result without having to press CTRL+SHIFT+ENTER. > > > > .- Hide quoted text - > > > > > > - Show quoted text - > > > > > > The sum formula won't work, because if for instance on one cell there > > is a +10 and on another a -10 it will indicate "row with no data" > > while there are values. Please let me know any other formula that > > would work. > > . > >
From: T. Valko on 28 Feb 2010 23:56
>for instance on one cell there is a +10 >and on another a -10 Try this, all on one line... =IF(SUM(COUNTIF(D1:CT1,{"<0",">0"})), "Row with data","Empty row") -- Biff Microsoft Excel MVP "Norvascom" <norvascom(a)gmail.com> wrote in message news:6cabcac2-dc7b-4c0a-bf8a-dd9c0666e32a(a)s36g2000prh.googlegroups.com... On Feb 28, 8:02 pm, Teethless mama <Teethlessm...(a)discussions.microsoft.com> wrote: > Try this: > > =IF(SUM(D1:CT1),"Row with data","Empty row") > > > > "Norvascom" wrote: > > On Feb 28, 4:01 pm, Lars-�ke Aspelin <lar...(a)REMOOVEtelia.com> wrote: > > > On Sun, 28 Feb 2010 12:37:37 -0800 (PST), Norvascom > > > > <norvas...(a)gmail.com> wrote: > > > >Hi, > > > > >I am trying to have a formula on cell A1 for example that would look > > > >on cells D1:CT1 to see if there are any values different than 0 (in > > > >other words not empty or equal to 0). > > > >If there are any values different than 0, cell A1 would indicate "Row > > > >with data" if none, it would say "Empty row". > > > > >Any help would be appreciated. > > > >Thanks > > > > Try this formula: > > > > =IF(OR(D1:CT1<>0),"Row with data","Empty row") > > > > Note: This is an array formula that should be confirmed with > > > CTRL+SHIFT+ENTER rather than just ENTER. > > > > Hope this helps / Lars-�ke > > > Thanks it works, but would there be any formula that would calculate > > automatically the result without having to press CTRL+SHIFT+ENTER. > > .- Hide quoted text - > > - Show quoted text - The sum formula won't work, because if for instance on one cell there is a +10 and on another a -10 it will indicate "row with no data" while there are values. Please let me know any other formula that would work. |