# 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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### 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
2
Views
284
Replies
7
Views
268
Replies
6
Views
385
Replies
0
Views
175
Replies
24
Views
473

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,940
Messages
5,834,495
Members
430,289
Latest member
Tphil413

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