Badly Needed community help

[Veltrex]

How do i count cell values in range that are continuously

Example: Column A was set to count only values more than 8.0. From #1-5 it would count 5, then #6&7 not included, then at number 8 counting start again as 1.
1. 8.1
2. 8.2
3. 8.3
4. 8.4
5. 8.6
6. 7.8
7. 7.7
8. 8.1
9. 8.2
10. 8.3
Guys can you help me what kind of formula should i use.

 

[awoohaw]

Thank you sir.. struggling to install XL2bb for some reason. what i need is (please check the photo) one cell only that will count the cell values that has color. Its like A4:A8 the counter cell A1 will count as 5 because the cells has color. Counter cell A1 will reset back to zero because A9 has no color, counter cell A1 again count as 1 since A10 has color, counter again reset to zero since A11 has no color and start counting again because A12:A25 cell has color so on and so fort.

thank you in advance...


View attachment 83532

Why is the answer in Thread #2 incorrect? Cell B1 counts only those with a value in column A:A that is greater than 8. If you will be adding data to the bottom then you'll need to either update the formula in B1 or convert the range to a table.

Here is a different version of the same solution:

WorkBook1.xlsx
	A	B
1	Counter:	8
2
3	01-23-2023 02:47:57 AM	8.1
4	01-23-2023 05:11:57 AM	8.2
5	01-23-2023 08:58:20 AM	8.3
6	01-23-2023 12:54:29 PM	8.4
7	01-23-2023 12:58:31 PM	8.6
8	01-23-2023 03:26:41 PM	7.8
9	01-23-2023 05:04:23 PM	7.7
10	01-23-2023 06:27:22 PM	8.1
11	01-23-2023 08:08:59 PM	8.2
12	01-23-2023 09:16:22 PM	8.3
13	01-23-2023 11:47:20 PM	8.3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(B3:B12,">"&8)

 

[Veltrex]

I think @Peter_SSs answered what you want in thread #8.
Here is his solution again:

WorkBook1.xlsx
	A	B
1	Num	Group Count
2	8.1
3	8.2
4	8.3
5	8.4
6	8.6	5
7	7.8
8	7.7
9	8.1
10	8.2
11	8.3	3
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(AND(A2>8,A3<=8),COUNTIF(A$1:A2,">8")-SUM(B$1:B1),"")

Counting would be accumulated if the cells continuously has color without breaking and then restarted counting again to 1 once found cell in color..

 

[Veltrex]

Counting would be accumulated if the cells continuously has color without breaking and then restarted counting again to 1 once found cell in color..

i mean it will not count all the cell with colors for the entire column.. the aim is to count only cell with color that is continuously without breaking and then restart counting from 1 if it breaks.

 

[awoohaw]

Counting would be accumulated if the cells continuously has color without breaking and then restarted counting again to 1 once found cell in color..

The photo you posted does not do as you describe.

Look in Post #3, Column C by @Peter_SSs? Does that answer your question?

 

[Peter_SSs]

. struggling to install XL2bb for some reason.

Look at these two threads. If there is still a problem with XL2BB, please explain exactly what the problem is.
xl2bb
Xl2bb got disable

what i need is (please check the photo) one cell only that will count the cell values that has color.

That is a completely different question to post #1!

In the picture in post #9, why is cell B1 = 34 when you say you want to count the coloured cells? There are not 34 coloured cells in that picture.

Does values > 8 (as stated in post #1) still have anything to do with your problem?

How are those cells coloured (manually, by vba code, by conditional formatting)?

 

[Veltrex]

The photo you posted does not do as you describe.

Look in Post #3, Column C by @Peter_SSs? Does that answer your question?

The photo you posted does not do as you describe.

Look in Post #3, Column C by @Peter_SSs? Does that answer your question?

It is in other perspective.. but i need only one cell that will count continuous cell with color without breaking.. Its like, in counter cell it will count only the event that happened that all cell that has color without breaking every moment i refresh my excel sheet.

 

[awoohaw]

@Veltrex , if you cannot get xl2bb to work, then please make a small example (10 rows) with the data you want compared, and the correct counts in the correct cells.

 

[awoohaw]

It is in other perspective.. but i need only one cell that will count continuous cell with color without breaking.. Its like, in counter cell it will count only the event that happened that all cell that has color without breaking every moment i refresh my excel sheet.

what do you mean by continuous? You have breaks in the cells that are are pink on only have one value and it is at the top. and then you do not resume counting anywhere else.

 

[awoohaw]

It is in other perspective.. but i need only one cell that will count continuous cell with color without breaking.. Its like, in counter cell it will count only the event that happened that all cell that has color without breaking every moment i refresh my excel sheet.

How do you expect to get an accurate answer if you are using another perspective? Again, if you cannot get xl2bb to work, then make a small matrix (10 x 2) with the EXACT data in Column 1, and the EXACT result you want to see in Column 2.

 

[Veltrex]

Look at these two threads. If there is still a problem with XL2BB, please explain exactly what the problem is.
xl2bb
Xl2bb got disable


That is a completely different question to post #1! (sorry )

In the picture in post #9, why is cell B1 = 34 when you say you want to count the coloured cells? There are not 34 coloured cells in that picture.

Does values > 8 (as stated in post #1) still have anything to do with your problem?

How are those cells coloured (manually, by vba code, by conditional formatting)?

That was countif function+conditional formatting. Sorry for the confusion to post 1
what i want to be is whenever i refresh excel sheet i what to see the counter if it has counted cell with color.
the counter will count only cell that has continuous color and stop and reset counting to zero once the continuous cell with color has been break.