check if any values on row

[Norvascom]

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

 

[Lars-Åke Aspelin]

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

 

[Norvascom]

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.

 

[Teethless mama]

Try this:

=IF(SUM(D1:CT1),"Row with data","Empty row")

 

[Norvascom]

Try this:

=IF(SUM(D1:CT1),"Row with data","Empty row")






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

 

[Teethless mama]

this one will do the trick

=IF(COUNT(D1:CT1)-COUNTIF(D1:CT1,0),"Row with data","Empty row")

 

[Teethless mama]

Another one...
=IF(MAX(INDEX(ABS(D1:CT1),)),"Row with data","Empty row")

 

[T. Valko]

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

Try this:

=IF(SUM(D1:CT1),"Row with data","Empty row")






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