Counting values in colums

Solved/Closed
gunnarb Posts 2 Registration date Wednesday January 26, 2011 Status Member Last seen January 27, 2011 - Jan 26, 2011 at 05:15 AM
 gunnarb - Feb 7, 2011 at 10:42 AM
Hello,
can you help, with this

i need to count the values in a colum

let's say, row 5, colums b5 to b15

in those coloms are numbers, 0 and 1

how can a check when there are 7*1 in a row
like
00101111111001

when theres 7*1 in a row to get some warning :)

thanks

Gunnar

9 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 7, 2011 at 08:57 AM
Hi Gunnar,

Take a look at your file:
https://authentification.site/files/26731266/Gunnar2.xls

The alfabet letters in B6:B27 have a conditional format. When 7 1's are next to each other the letter turns blue.

Note: the formulas used in row 45:66 can be hidden or turn the text color to white, to keep your sheet look clean.

Is this better?

Best regards,
Trowa
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 27, 2011 at 09:07 AM
I think you need vba to do that. Is this waring to show up as you type in number or does it need to show up in some other way ?
gunnarb Posts 2 Registration date Wednesday January 26, 2011 Status Member Last seen January 27, 2011
Jan 27, 2011 at 09:53 AM
hi

when there is 7 in a row, it should just display somthing at the end colum
like when you input 1, 7 times in a row it lets you know

thanks
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jan 31, 2011 at 09:35 AM
Hi Gunnarb,

You could use an extra column next to B (or wherever).
Use a simple SUM formula to count seven cells.
At the end of the column use the following formula:
=SUM(IF(C8:C23=7,1,0))
This is an array formula, confirm by hitting Ctrl+Shift+Enter.

When this formula shows a 1 you know 7 times a 1 are below each other.
If a 0 shows up this is not the case.

Does this work for you?

Best regards,
Trowa
hi

i need somthing for colums
like c8 to j8

thanks

Gunnar
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 1, 2011 at 09:25 AM
Hi Gunnar,

The strategy remains the same.
Take a look at the following file:

https://authentification.site/files/26620522/Gunnar.xls

Hope this helps.

Best regards,
Trowa
thanks for that, i might be able to use that
this is the table i am trying to use this in

https://authentification.site/files/26634931/v2011.xls

thanks

Gunnar
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 3, 2011 at 08:53 AM
So case solved?
no cant get it to work if there are more then 1 row of colums
hi

this is great :)

thanks for the help

Gunnar