# range of cells satisfying a condition

#### dharsana

##### Board Regular
hi,
This should be a simple one but I am unable to do it.
I have a range of cells which contains a formula.These cells diplay 0 value as a formula result.I want to have a formula in another cell which sholud return a value(say "true")if all the cells in the range shows zero value.Even if one cell contains other than zero then the result shuold return false.
pl help

dharshana

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Barry Katcher

##### Well-known Member
Easy way but not foolproof:

Careful, though. If you have positive and negative numbers in the cells AND THEY NET OUT TO ZERO, you'll get a "true".

##### MrExcel MVP
On 2002-10-27 13:09, dharsana wrote:
hi,
This should be a simple one but I am unable to do it.
I have a range of cells which contains a formula.These cells diplay 0 value as a formula result.I want to have a formula in another cell which sholud return a value(say "true")if all the cells in the range shows zero value.Even if one cell contains other than zero then the result shuold return false.
pl help

dharshana

How about an empty cell when you state: "Even if one cell contains other than zero then the result shuold return false"?

#### Barry Katcher

##### Well-known Member
Aladin, it's Sunday. What the hell are we doing on here?

##### MrExcel MVP

On 2002-10-27 13:30, Barry Katcher wrote:
Aladin, it's Sunday. What the hell are we doing on here?

That's a hard one...

#### Yogi Anand

##### MrExcel MVP
Hi dharsana:

see following worksheet simulation wherein I have used the formula array-based formula

'=IF(SUM(IF(ISNUMBER(B1:B6),IF(B1:B6=0,1,0)))=MATCH(9.99E+307,B:B)-ROW(A1)+1,TRUE,FALSE)
y021027h1.xls
ABCD
110FALSE
240
370
421
5k0
6k0
Sheet2
</SPAN>

Regards!

Yogi Anand

#### Yogi Anand

##### MrExcel MVP

Hi dharsana:

And if one of the cells in the range of interest is empty, it will return a FALSE, it will return TRUE only if all of the cells of interest house only 0
y021027h1.xls
ABCD
110FALSE
240
370
42
5k0
6k0
Sheet2
</SPAN>

Regards!

Yogi Anand

##### MrExcel MVP
There must be a correlation between my posing a question and "worksheet simulations" flying in.

Well, I'll just assume that a formula returned blank or simply an empty cell must be considered also as reason for returning FALSE. If so, there is no need for an additional column and an "array-based" formula...

=ROWS(A1:A5)=COUNTIF(A1:A5,0)

will suffice. Adjust the range to suit.

#### Yogi Anand

##### MrExcel MVP
On 2002-10-27 14:21, Aladin Akyurek wrote:

There must be a correlation between my posing a question and "worksheet simulations" flying in.

Well, I'll just assume that a formula returned blank or simply an empty cell must be considered also as reason for returning FALSE. If so, there is no need for an additional column and an "array-based" formula...

=ROWS(A1:A5)=COUNTIF(A1:A5,0)

will suffice. Adjust the range to suit.

Your formula is beautiful -- short and sweet! I took a long convoluted route to do the same thing. Thank You.

Regards!

Yogi Anand

Replies
13
Views
233
Replies
6
Views
175
Replies
3
Views
89
Replies
6
Views
106
Replies
10
Views
123

1,128,167
Messages
5,629,079
Members
416,364
Latest member
maatpsr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back