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.

Jack

Posted 2014-07-29T14:20:23.513

Reputation: 41

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)

Excellll

Posted 2014-07-29T14:20:23.513

Reputation: 11 857

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.

g.kov

Posted 2014-07-29T14:20:23.513

Reputation: 839

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 – 2015-06-24T00:08:22.310

@danicotra: Thanks, just arithmetic MAX is a known replacement for logical OR, it is essentially the same as =OR(B2:B30)*1. – g.kov – 2015-06-24T01:11:06.873

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 – 2015-06-24T18:18:40.290

I suppose:

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

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

Andrew Kelly

Posted 2014-07-29T14:20:23.513

Reputation: 11

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

DaveRGP

Posted 2014-07-29T14:20:23.513

Reputation: 151