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

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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)
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 '15 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 '15 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 '15 at 18:18

I suppose:

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

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

 
discard

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.