| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count filled cells in range if they are one after the other
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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),"")) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
It is working.
Thanks You are great, best regards Ivo "T. Valko" wrote in message ... 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),"")) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "lopina" wrote in message ... It is working. Thanks You are great, best regards Ivo "T. Valko" wrote in message ... 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),"")) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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" wrote in message ... You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "lopina" wrote in message ... It is working. Thanks You are great, best regards Ivo "T. Valko" wrote in message ... 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),"")) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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? -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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" wrote in message ... You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "lopina" wrote in message ... It is working. Thanks You are great, best regards Ivo "T. Valko" wrote in message ... 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),"")) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get a count of number of cells filled in? | Excel Discussion (Misc queries) | |||
| Count filled colour in cell in given range | Excel Discussion (Misc queries) | |||
| I need formula that will automatically count the filled cells. | Excel Discussion (Misc queries) | |||
| Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions | |||
| Count Rang of Filled-In Cells | Excel Worksheet Functions | |||
Hybrid Mode
