Take the 2-minute tour ×
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:

=IF(SUM(B2:B30)>0,1,0)
share|improve this answer

If there are always either 0 or 1 values than simple =MAX(B2:B30) is enough.

share|improve this answer

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.