Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a `0` if a feature isn't present, and a `1` if it is.

What I want to do is get excel to check a column, lets say `B2:B30`, and, if there is a `1` in any of those rows to show a `1` in `B1`. If there is a `0` in all the rows, then I need it to show a `0`.

Looked at lots of stuff online, and I can get it to count the number of rows with a certain number (or just use a pivot table), but I can't get it to roll up and just show a simple `0` or `1`.

Any help would be great.

-

4 Answers

You just need to wrap whatever formula you're using now in an `IF` statement that returns `1` for the condition being met and `0` for not.

For example:

``````=IF(SUM(B2:B30)>0,1,0)
``````
-

If the column's cells always contain either `0` or `1` as values (and what you want as a result is 1 when there's at least a 1 in any of the cells and 0 otherwise) then a simple `=MAX(B2:B30)` is enough.

-
Yeah... so simple so clear, why make things complex when there's no need? I think this one should definitively be the accepted answer +1! –  danicotra Jun 24 at 0:08
@danicotra: Thanks, just arithmetic `MAX` is a known replacement for logical `OR`, it is essentially the same as `=OR(B2:B30)*1`. –  g.kov Jun 24 at 1:11
You actually have only 2 answers on superuser but I simply remained jaw-dropped astonished and amazed by your overturning approach to problems/solutions. In your other answer you broke the "classical scheme" compared to others finding a blazing (and uncommon) solution. In this one you got straight to the point quickly noticing there was a trivial solution. I think you must be some kind of genius, if this were Twitter you'd have me among your brand new followers! :D –  danicotra Jun 24 at 18:18

I suppose:

``````    =IF(COUNTIF(B2:B30)=0,0,1)
``````

would work too, but maybe Excellll's one is marginally faster.

-

If by 'roll up' you mean group all of the 0 in one area, and all the 1 in another then sort the table by 0 and 1 (i.e. sort by column b), then use the 'Group' function

-