counting values in columsSolved/Closed

Question

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

rizvisa1 · Answer

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 · Answer

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 · Answer

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

gunnarb · Answer

hi

i need somthing for colums
like c8 to j8

thanks

Gunnar

TrowaD · Answer

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

GunnarB · Answer

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

GunnarB · Answer

no cant get it to work if there are more then 1 row of colums

TrowaD · Answer

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

gunnarb · Answer

hi

this is great :)

thanks for the help

Gunnar

Counting values in colums

9 replies

Subscribe To Our Newsletter!