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.

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)
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.

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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