Count filled cells in range if they are one after the other

[lopina]

Hello to all!!

I have one question if anybody can help me, in range I have to calculate how
many filled cell is they are one ater the oher.

Example:
A
AE
3 3 1 1 2 2 3 3 1 1 1 1 1 1 1 2 2 2 2 2 2


Result if possible:

2filled 6filled 7filled 6filled

If somebody understand what I meen don't fear to help.

Thanks
Best regards
Ivo

 

[T. Valko]

Try this...

There *must* be an empty cell at the end of the range.

Let's assume the range of data is B1:Z1. Cell AA1 *must* be an empty cell.

Enter this formula in B2:

=IF(B1="","",IF(AND(B1<>"",C1=""),1,""))

Enter this formula in C2 an copy across to Z2:

=IF(C1="","",IF(AND(C1<>"",D1=""),COUNTA($B1:C1)-SUM($B2:B2),""))

 

[lopina]

It is working.

Thanks You are great,

best regards
Ivo

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[lopina]

I have one more question.


sheet1
3 3 1 1 2 2 3 3 1 1 1 1 1 1 1 2 2 2 2 2 2


sheet2
A1=7


Let say I have theese thata in sheet1, on sheet2 in cell A1 I need counting
only line of numbers that is bigger then 6 in a row in thesse range on
sheet1.
Is it possibly I can not figure out.

best regards
Ivo

 

[T. Valko]

I'm not sure I understand.

sheet1
3 3...1 1 2 2 3 3...1 1 1 1 1 1 1...2 2 2 2 2 2

You want to count how many consecutive "runs" there are that are >6? So, in
the above sample the result would be 1?

 

[lopina]

Yes the result would be 1.

You are right

best regards
Ivo

 

[T. Valko]

Ok, you could use the formulas for your other question then just count how
many are >6:

=COUNTIF(B2:Z2,">6")

Or, you can use this array formula** :

=SUM(IF(FREQUENCY(IF(B1:Z1<>"",COLUMN(B1:Z1)),IF(B1:Z1="",COLUMN(B1:Z1)))>6,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[lopina]

Hello

this is excelent, You sve me lot of work.

best regards
Ivo

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Hello

this is excelent, You sve me lot of work.

best regards
Ivo

 

[lopina]

Hello

one more question, is it posiblle:


If cell=IS then is empty cell.

If I have

3 3...1 1 2 2 3 3...1 1 1 IS 1 1 1 ...2 2 2 2 2 ...

Then result for this formula is 0.

=SUM(IF(FREQUENCY(IF(B1:Z1<>"",COLUMN(B1:Z1)),IF(B1:Z1="",COLUMN(B1:Z1)))>6,1))



best regards
Ivo

 

[T. Valko]

Maybe this...

Array entered:

=SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1)),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))>6,1))

 

[lopina]

Hello

not working, it should only not count text "IS" other text can count.

Anyway thanks for response.

best regards
Ivo

 

[T. Valko]

it should only not count text "IS"

other text can count

Try this...

Array entered:

=SUM(IF(FREQUENCY(IF(B1:Z1<>"",COLUMN(B1:Z1)),IF((B1:Z1="IS")+(B1:Z1=""),COLUMN(B1:Z1)))>6,1))

 

[lopina]

Yes this is realy close, now I think that You can do anything.

I have more conditon :

3 3...1 1 2 IS 3 33...1 1 1 IS 1 1 1 1...2 2 2 2 2 2 2...

If I have IS in consecutive run that is bigger then 7, I need result 2 -
for this example.
For me IS it should be ignored..


best regards
Ivo

 

[T. Valko]

I need result 2 - for this example.

I only see one that's bigger than 7:

3 3 = 2
1 1 2 IS 3 33 = 6
1 1 1 IS 1 1 1 1 = 8
2 2 2 2 2 2 2 = 7

Wouldn't this scenario be the same as the earlier one where you just wanted
to count consecutive "runs" of non-empty cells?

 

[lopina]

Sorry, may mistake.

If I have IS in consecutive run that is bigger then 6, I need result 2 -
for this example.

best regards
Ivo

 

[T. Valko]

I think this formula from an earlier reply will do that:

=SUM(IF(FREQUENCY(IF(B1:Z1<>"",COLUMN(B1:Z1)),IF(B1:Z1="",COLUMN(B1:Z1)))>6,1))