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]

1. What do you mean by "refresh" sheet? New data being added line by line, a brand new set of data. Do you have Manual Calculation turned on?
2. If there are more than 3 "breaks" how many counters do you want to see? What do you want to see if the last record is <8 or not colored?
3. Do you want the cells >8 counted or the cells that are colored counted?

I implore you to please give an exact example of what you want the spreadsheet to look like after you do a "refresh"?

 

[Veltrex]

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

1. What do you mean by "refresh" sheet? New data being added line by line, a brand new set of data. Do you have Manual Calculation turned on?
2. If there are more than 3 "breaks" how many counters do you want to see? What do you want to see if the last record is <8 or not colored?
3. Do you want the cells >8 counted or the cells that are colored counted?

I implore you to please give an exact example of what you want the spreadsheet to look like after you do a "refresh"?

Just a short info what im doing, im using excel to export data from historian server..it is not live data .. i need to refresh excel sheet from time to time to get the data history.
in attached file there was a time and on the top is the counter..
Attached example sheet was opened at 0:24H and the counter could see on top...

sorry sir this is the last, even me im tired of this thanks for the inputs

 

[awoohaw]

Your task is not hard to accomplish, but you need to provide a better description of what you want.
The picture above has values less than 8 that are colored. Not sure why the time the file is refreshed is so critical, but that is your concern. But, if you want counts you need to give accurate expectations. You've received numerous examples of how to use countif. If you are focused on color you can query the cell color.

I asked to give a small matrix (10 x 2), why send another confusing photograph? anyway, best wishes.

 

[awoohaw]

So, you only want a count of the LAST records in the column that are "Above 8" or "Colored"?
=-------- But, you have values less than 8 that are colored ------ please explain what you want counted?

As far as the refresh, most calculations will occur automatically. You would probably have to update formula ranges. when you add new data.

 

[Veltrex]

So, you only want a count of the LAST records in the column that are "Above 8" or "Colored"?
=-------- But, you have values less than 8 that are colored ------ please explain what you want counted?

As far as the refresh, most calculations will occur automatically. You would probably have to update formula ranges. when you add new data.

coloured sir

 

[awoohaw]

I looked into getting the background color as a data element to use to compare cells. But, I could not find that.
What are the data conditions that cause you to highlight the cell with a color? I also, am not sure how to create a formula that looks backwards from the last value in the column without a lot of recursion (LAMBDA functions - which I don't think is available in Excel 2019).

I just had a thought, MAYBE the last "FALSE" condition in a column could be located, and the number of that could be subtracted from the total column count to get the count.

 

[Veltrex]

I looked into getting the background color as a data element to use to compare cells. But, I could not find that.
What are the data conditions that cause you to highlight the cell with a color? I also, am not sure how to create a formula that looks backwards from the last value in the column without a lot of recursion (LAMBDA functions - which I don't think is available in Excel 2019).

I just had a thought, MAYBE the last "FALSE" condition in a column could be located, and the number of that could be subtracted from the total column count to get the count.

its just a conditional formatting.. the counter were manual counting..i just made that for illustration only

 

[awoohaw]

Well, if you are arbitrarily selecting cells to fill, I don't think a formula will always work for you. But, if it is indeed conditional.... what are your conditional formatting rules... those would be the rules to put in the formula!!!!

But, I was successful in getting it to work based on counting the last consective records greater than 8:

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H
3	TIME\COUNT	3.00	1.00	2.00	1.00	0.00	0.00	2.00
4	00:00:01	8.02	7.96	8.32	7.83	8.15	8.01	8.33
5	00:00:02	7.62	8.17	7.99	8.32	7.54	8.18	7.89
6	00:00:03	7.55	8.48	8.18	7.85	7.77	7.99	8.13
7	00:00:04	7.99	8.22	8.33	8.01	8.29	7.94	8.09
8	00:00:05	7.91	7.99	7.97	7.65	8.30	8.29	8.02
9	00:00:06	8.38	7.62	7.67	8.26	8.19	8.37	7.74
10	00:00:07	7.87	8.20	7.78	8.47	7.64	8.48	7.55
11	00:00:08	8.00	8.28	7.60	8.15	8.50	8.21	7.62
12	00:00:09	8.01	7.84	8.05	7.73	7.99	8.04	8.23
13	00:00:10	8.25	8.29	8.40	8.17	7.63	7.91	8.44
COUNTIF

Cell Formulas
Range	Formula
B3:H3	B3	=ROWS(B4:B13)-MAX((ROW(B4:B13)-MIN(ROW(B4:B13))+1)*(B4:B13<8))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:H13	Expression	=B4>=8	text	NO

 

[Veltrex]

Well, if you are arbitrarily selecting cells to fill, I don't think a formula will always work for you. But, if it is indeed conditional.... what are your conditional formatting rules... those would be the rules to put in the formula!!!!

But, I was successful in getting it to work based on counting the last consective records greater than 8:

Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H
3	TIME\COUNT	3.00	1.00	2.00	1.00	0.00	0.00	2.00
4	00:00:01	8.02	7.96	8.32	7.83	8.15	8.01	8.33
5	00:00:02	7.62	8.17	7.99	8.32	7.54	8.18	7.89
6	00:00:03	7.55	8.48	8.18	7.85	7.77	7.99	8.13
7	00:00:04	7.99	8.22	8.33	8.01	8.29	7.94	8.09
8	00:00:05	7.91	7.99	7.97	7.65	8.30	8.29	8.02
9	00:00:06	8.38	7.62	7.67	8.26	8.19	8.37	7.74
10	00:00:07	7.87	8.20	7.78	8.47	7.64	8.48	7.55
11	00:00:08	8.00	8.28	7.60	8.15	8.50	8.21	7.62
12	00:00:09	8.01	7.84	8.05	7.73	7.99	8.04	8.23
13	00:00:10	8.25	8.29	8.40	8.17	7.63	7.91	8.44
COUNTIF

Cell Formulas
Range	Formula
B3:H3	B3	=ROWS(B4:B13)-MAX((ROW(B4:B13)-MIN(ROW(B4:B13))+1)*(B4:B13<8))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:H13	Expression	=B4>=8	text	NO

Oh man..! this is exactly what i need...sir awoohaw thank you it works.....thank you for the time you spent just to give me an answer...i really really appreciate it...thank you also peter for the input.....you guys are amazing thank you again....

 

[awoohaw]

You are welcome. I enjoyed the challenge.
Please get xl2bb for future questions.