count number of cells
I need a formula to count the number of cells in a column that have numbers
in them and are less than 46 (or cell B2 which equals to 46), but stops at a blank cell , and then counts again how many cells have numbers in them less than 46 (or B2) but stops at the next blank cell and so on eg: A B 1 71 2 55 3 36 4 72 5 15 6 80 7 95 result 2 8 blank cell 9 17 10 22 11 90 12 35 result 3 13 blank cell 14 15 15 20 16 23 17 44 18 100 result 4 19 blank cell 20 21 21 17 result 2 22 blank cell down to 500 rows Any help will appreciated thanks bill gras bill gras |
Hi!
I don't understand your explanation. Your sample data seems to span A1:A21 (excluding the blank cell in A22) and column B is blank except for the desired results yet you're are making some sort of reference to cell B2 (which you say equals 46). Is this reference to cell B2 (that equals 46) on a different sheet? Here's one way based on the posted sample data: In B1 enter 0 In B2 enter this formula as an array using the key combo of CTRL,SHIFT,ENTER: =IF(A2="",0,IF(AND(A2<"",A3=""),COUNTIF(INDIRECT( "A"&MAX((B$1:B1<"")*(ROW(B$1:B1)))):A2,"<46"),"") ) Copy down as needed. Where blank cells appear in column A the corresponding cell in column B will have a zero. If you don't want to see these then you can use a custom format to hide them: 0;-0;;@ Biff "bill gras" wrote in message ... I need a formula to count the number of cells in a column that have numbers in them and are less than 46 (or cell B2 which equals to 46), but stops at a blank cell , and then counts again how many cells have numbers in them less than 46 (or B2) but stops at the next blank cell and so on eg: A B 1 71 2 55 3 36 4 72 5 15 6 80 7 95 result 2 8 blank cell 9 17 10 22 11 90 12 35 result 3 13 blank cell 14 15 15 20 16 23 17 44 18 100 result 4 19 blank cell 20 21 21 17 result 2 22 blank cell down to 500 rows Any help will appreciated thanks bill gras bill gras |
Hi!
Something I thought about after I had logged off for the evening..... I guess it's possible for the first entry, A1, to be <46 and A2 could be blank. So, in B1 instead of entering zero enter this formula: =(A1<46)*1 Biff "Biff" wrote in message ... Hi! I don't understand your explanation. Your sample data seems to span A1:A21 (excluding the blank cell in A22) and column B is blank except for the desired results yet you're are making some sort of reference to cell B2 (which you say equals 46). Is this reference to cell B2 (that equals 46) on a different sheet? Here's one way based on the posted sample data: In B1 enter 0 In B2 enter this formula as an array using the key combo of CTRL,SHIFT,ENTER: =IF(A2="",0,IF(AND(A2<"",A3=""),COUNTIF(INDIRECT( "A"&MAX((B$1:B1<"")*(ROW(B$1:B1)))):A2,"<46"),"") ) Copy down as needed. Where blank cells appear in column A the corresponding cell in column B will have a zero. If you don't want to see these then you can use a custom format to hide them: 0;-0;;@ Biff "bill gras" wrote in message ... I need a formula to count the number of cells in a column that have numbers in them and are less than 46 (or cell B2 which equals to 46), but stops at a blank cell , and then counts again how many cells have numbers in them less than 46 (or B2) but stops at the next blank cell and so on eg: A B 1 71 2 55 3 36 4 72 5 15 6 80 7 95 result 2 8 blank cell 9 17 10 22 11 90 12 35 result 3 13 blank cell 14 15 15 20 16 23 17 44 18 100 result 4 19 blank cell 20 21 21 17 result 2 22 blank cell down to 500 rows Any help will appreciated thanks bill gras bill gras |
Hi Biff
In cell B2 is a formula that equals to 46 which I forgot to put in. It works perfectly! and once again Thank You regards bill gras -- bill gras "Biff" wrote: Hi! Something I thought about after I had logged off for the evening..... I guess it's possible for the first entry, A1, to be <46 and A2 could be blank. So, in B1 instead of entering zero enter this formula: =(A1<46)*1 Biff "Biff" wrote in message ... Hi! I don't understand your explanation. Your sample data seems to span A1:A21 (excluding the blank cell in A22) and column B is blank except for the desired results yet you're are making some sort of reference to cell B2 (which you say equals 46). Is this reference to cell B2 (that equals 46) on a different sheet? Here's one way based on the posted sample data: In B1 enter 0 In B2 enter this formula as an array using the key combo of CTRL,SHIFT,ENTER: =IF(A2="",0,IF(AND(A2<"",A3=""),COUNTIF(INDIRECT( "A"&MAX((B$1:B1<"")*(ROW(B$1:B1)))):A2,"<46"),"") ) Copy down as needed. Where blank cells appear in column A the corresponding cell in column B will have a zero. If you don't want to see these then you can use a custom format to hide them: 0;-0;;@ Biff "bill gras" wrote in message ... I need a formula to count the number of cells in a column that have numbers in them and are less than 46 (or cell B2 which equals to 46), but stops at a blank cell , and then counts again how many cells have numbers in them less than 46 (or B2) but stops at the next blank cell and so on eg: A B 1 71 2 55 3 36 4 72 5 15 6 80 7 95 result 2 8 blank cell 9 17 10 22 11 90 12 35 result 3 13 blank cell 14 15 15 20 16 23 17 44 18 100 result 4 19 blank cell 20 21 21 17 result 2 22 blank cell down to 500 rows Any help will appreciated thanks bill gras bill gras |
You're welcome. Thanks for the feedback!
Biff "bill gras" wrote in message ... Hi Biff In cell B2 is a formula that equals to 46 which I forgot to put in. It works perfectly! and once again Thank You regards bill gras -- bill gras "Biff" wrote: Hi! Something I thought about after I had logged off for the evening..... I guess it's possible for the first entry, A1, to be <46 and A2 could be blank. So, in B1 instead of entering zero enter this formula: =(A1<46)*1 Biff "Biff" wrote in message ... Hi! I don't understand your explanation. Your sample data seems to span A1:A21 (excluding the blank cell in A22) and column B is blank except for the desired results yet you're are making some sort of reference to cell B2 (which you say equals 46). Is this reference to cell B2 (that equals 46) on a different sheet? Here's one way based on the posted sample data: In B1 enter 0 In B2 enter this formula as an array using the key combo of CTRL,SHIFT,ENTER: =IF(A2="",0,IF(AND(A2<"",A3=""),COUNTIF(INDIRECT( "A"&MAX((B$1:B1<"")*(ROW(B$1:B1)))):A2,"<46"),"") ) Copy down as needed. Where blank cells appear in column A the corresponding cell in column B will have a zero. If you don't want to see these then you can use a custom format to hide them: 0;-0;;@ Biff "bill gras" wrote in message ... I need a formula to count the number of cells in a column that have numbers in them and are less than 46 (or cell B2 which equals to 46), but stops at a blank cell , and then counts again how many cells have numbers in them less than 46 (or B2) but stops at the next blank cell and so on eg: A B 1 71 2 55 3 36 4 72 5 15 6 80 7 95 result 2 8 blank cell 9 17 10 22 11 90 12 35 result 3 13 blank cell 14 15 15 20 16 23 17 44 18 100 result 4 19 blank cell 20 21 21 17 result 2 22 blank cell down to 500 rows Any help will appreciated thanks bill gras bill gras |
All times are GMT +1. The time now is 10:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com