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 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
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
1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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 ?
0
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
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
0

Didn't find the answer you are looking for?

Ask a question
hi

i need somthing for colums
like c8 to j8

thanks

Gunnar
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
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
0
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
513
Feb 3, 2011 at 08:53 AM
So case solved?
0
no cant get it to work if there are more then 1 row of colums
0
hi

this is great :)

thanks for the help

Gunnar
0