Share via

Excel Check if range contains number, negative or positive

Anonymous
2013-09-12T22:13:27+00:00

How can I identify if a range contains data. I am looking for a formula which I can drag  down to check  a range.

The range will contain either 0, or a positive or negative number.

I have added a helper column, using a SUM to SUM range- however if  the range contains, for example +5 and (5), result is zero. If the range contains 0 + 0= same result zero.

I want to be able to identify if the result is a positive number, negative or zero, but to ignore if all cells in the range contain zero.

I have attempted to solve this by issuing ISNUMBER , returns a result TRUE even if result is zero, ISBLANK also does not hep as all cells contain something.

I hope I have explained this clearly. I am sure there is a simple solution, but this has eluded me so far.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-09-13T01:16:41+00:00

    How about something like,

    =IF(COUNTIFS(<range>, "<>"&0,<range>, "<>"), SUM(<range>), "")

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 100.9K Reputation points Volunteer Moderator
    2013-09-13T01:20:34+00:00

    Hi,

    Please take a clear example of your dataset and also share the expected solution.

    0 comments
  2. Anonymous
    2013-09-13T03:01:02+00:00

    Thank you, just what i was attempting  to solve...

    0 comments