Count consecutive increase values with blank cell in Excel

2
votes

I have a formula for counting consecutive increase of values in a row range. It seem to work properly with #N/A values (which I have in my dataset) but I get some issues with the first blank value in a row. In my example below I should have 3 increases (green cells) for both rows.

Formulas are (they are array formulas, Ctrl + Shift + Enter):

J2: {=SUM(IFERROR(--((C2:H2>B2:G2)=TRUE);0))}

J3: {=SUM(IFERROR(--((C3:H3>B3:G3)=TRUE);0))}

It seems that formula in J3 count D3 as 0 and therefore count an increase from 0 to 0,22.

enter image description here

2
arraysexcelexcel-formula

2 Answers

4
votes

Use this array formula finishing it with ctrl+shift+enter instead of just enter.

=SUMPRODUCT(IFERROR((C2:H2>B2:G2), 0)*IFERROR(B2:G2<>"", 0))
0
votes

I think you're right about blank being treated as 0. I suggest dividing your first logical equation (C3:H3>B3:G3)=TRUE by B3:G3<>"", resulting in a divide-by-zero error on blanks:

J3: {=SUM(IFERROR(--((C3:H3>B3:G3)=TRUE / ((B3:G3)<>""));0))}

Does this fix it for you?