4

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.

Improve this question

4 Answers 4

Reset to default
6

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)
Improve this answer
5

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.

Improve this answer
3
  • 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, 2015 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, 2015 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, 2015 at 18:18
1

I suppose:

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

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

Improve this answer
0

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .