Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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:

share|improve this answer

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.

share|improve this answer
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:


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

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.