exclude 0 and blank cells

Please explain specifically what you are attempting to do. Please describe your worksheet and/or attach a sample worksheet with an explanation. A formula supplied that is not working will not tell us what it should be doing.

Formula:

Please Login or Register  to view this content.

Sheet1
A1 value 962
A2 value 855
A3 value 622
A4 value 777
A5 value 611
A6 value 0
A7 value empty
Sheet2
A1 value 581
A2 value 579

The result must be 6, not 5. The current formula can't follow after 0 cell value and empty cells.
I don't know where's my fault.

Best Regards!

You have not explained what you wish to accomplish. You have shown me a formula and some data but no explanation of what you want to happen. I ask, because fixing existing formulas may not be the solution, but rather creating a newer or different way may be the direction to fix the issue. Help us to help you by answering the questions directly.

Originally Posted by alansidman

You have not explained what you wish to accomplish. You have shown me a formula and some data but no explanation of what you want to happen. I ask, because fixing existing formulas may not be the solution, but rather creating a newer or different way may be the direction to fix the issue. Help us to help you by answering the questions directly.

Sorry,

I want to count in cell AA1 (sheet1), the valor between last >=580 and reset after the value are <=579. The data has coming from websource, connexion (not macros or manually), there are refresh every 5 minutes, and every 5 minutes added automatically a new row. The problem is that, the source have a line with one 0 value (the last row (159), and become the second line to 1:00am), and this line/row, will move down every 5 minutes, on this column that monitoring, exist a formula:

Formula:

Please Login or Register  to view this content.

, and this formula fill down from this row, but not data on these rows, columns C-V. Also, I have created a table with 160 rows, and this is the reason that I have empty cells after this row with 0 value in sheet1.

I have used this formula actually:

Formula:

Please Login or Register  to view this content.

Originally Posted by zbor

Can you explain step by step what you trying to do and upload example with expected result and problematic output that gives you wrong result.
So you can also paint in other color cells that doesn't match in your case.

Hello,

As we can see in the attached file, as an example:
1: I have a value 500 in Sheet2 (Results) cell W6, between this last and new value >=580 I want to calculate.
2: The result at this moment in AA1 is 132 (wrong), that because I count from Sheet1 $W$3:$W$159 up to Sheet2 (Results) $W$3:$W$159, only from the last <=579 value
3: The result must be 135 to be correctly (because 132 Draws from Sheet1 + 3 Draws (rows 3,4,5) in Sheet2(Results) = 135, as you can see in the file attached, the formula can't follow rows with 0 (row 135) and empty cells (rows 136:160) to continue search in next file (Results).
4: If new entry row will added (all new entry rows will be W3 and old W3 line will be W4...) in Sheet1, and the value will be <=579 the result in AA1 must be 0, after, if a new entry will added and value will be 666, the result in cell AA1 must be 1 because on row W4 we have <=579 value (this value in my case, are Draws between last low value, and reset when new value will be <=579). Count all high value till new low value appear. All data from Sheet2 (Results) are fixed, there will not be empty cells or 0 value. The problem is Sheet1, here import external data from websource, and that's why all time I have this line with 0 value, the empty cells that's exist, exist because I have make the table before with all lines which can be extracted.

So, I want to exclude this cell with 0 value and empty cells from Sheet1 that's exist under last line extracted where the value will be always >=210 in column W.

My formula stop before 0 value or empty cells, even if there I have this condition: ($W$3:$W$159>0).

Best Regards!

How about:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.