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

  • Thread starter Thread starter lopina
  • Start date Start date
L

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
 
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),""))
 
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
 
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
Click to expand...

You want to count how many consecutive "runs" there are that are >6? So, in
the above sample the result would be 1?
 
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.
 
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
 
Maybe this...

Array entered:

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

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

Anyway thanks for response.

best regards
Ivo
 
it should only not count text "IS"
other text can count
Click to expand...

Try this...

Array entered:

=SUM(IF(FREQUENCY(IF(B1:Z1<>"",COLUMN(B1:Z1)),IF((B1:Z1="IS")+(B1:Z1=""),COLUMN(B1:Z1)))>6,1))
 
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
 
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?
 
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
 
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))
 
Back
Top