| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
Yes the result would be 1.
You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
Hello
this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
You're welcome!
-- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
Maybe this...
Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
Hello
not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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" wrote in message ... 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)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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? -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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" wrote in message ... 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)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
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" wrote in message ... 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? -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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" wrote in message ... 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)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count filled cells in range if they are one after the other
I think this formula from an earlier reply will do that:
=SUM(IF(FREQUENCY(IF(B1:Z1<"",COLUMN(B1:Z1)),IF(B 1:Z1="",COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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" wrote in message ... 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? -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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" wrote in message ... 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)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello not working, it should only not count text "IS" other text can count. Anyway thanks for response. best regards Ivo "T. Valko" wrote in message ... Maybe this... Array entered: =SUM(IF(FREQUENCY(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1) ),IF(NOT(ISNUMBER(B1:Z1)),COLUMN(B1:Z1)))6,1)) -- Biff Microsoft Excel MVP "lopina" wrote in message ... 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(B 1:Z1="",COLUMN(B1:Z1)))6,1)) best regards Ivo "T. Valko" wrote in message ... You're welcome! -- Biff Microsoft Excel MVP "lopina" wrote in message ... Hello this is excelent, You sve me lot of work. best regards Ivo "T. Valko" wrote in message ... 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(B 1: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. -- Biff Microsoft Excel MVP "lopina" wrote in message ... Yes the result would be 1. You are right best regards Ivo "T. Valko" wrote in message ... 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 | |||
Linear Mode
