Incrementing a number in an array formula?

[azairvine]

I have created an array formula that checks specific conditions in a row, and returns results based on those criteria. The conditions are as follows:

1. Condition A not met - number stays the same
2. Condition A met (but not all three conditions) - increment number
3. All three conditions met - set number to 0

For some reason I cannot get the array to increment the number in the result - full disclosure, I don't really understand array formulas too well so it may be something obvious.

Here's what I would expect to get:

​

[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: center"]Con A[/TD]
[TD="align: center"]Con B[/TD]
[TD="align: center"]Con C[/TD]
[TD="align: center"]Array[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

My formula included when condition (2) was met, it would increment the "Array" column by one. For example the array formula would be entered in D4 and contain "D3+1" when condition (2) was met. This works for a non-array formula, but doesn't seem to work for an array formula. It's not populating a running total down the array as I had expected. It appears to be treating each previous cell in the column as having a value of zero, or no value - hence I cannot create an incremental value.

 

[James006]

Hi,

Why don't you post this Array Formula ... which needs to be slightly modified ... ???

 

[azairvine]

Hi,

Why don't you post this Array Formula ... which needs to be slightly modified ... ???

Because the actual formula I'm working on is ridiculously more complex than this example and I'm afraid that'll dissuade a response. There are so many conditions that need to be checked in each row anyone would take one look at it and close their browser tab!

I figured if I could get the basic structure of the formula I could extrapolate it out myself.

The root of my problem is, why can't array formula increment values?

 

[Rick Rothstein]

The following will only work if your first data item is in Row 2 (Row 1 can either be blank or contain headers). It also assumes that the cells you show as blank are truly empty (that is, Columns A through C do NOT contain formulas display "" for those blanks). If your data can meet those conditions, then place this normally entered formula in cell D2 and copy it down to the end of your data...

=IF(COUNTA(A2:C2)=3,0,N(D1)+1)

 

[azairvine]

The following will only work if your first data item is in Row 2 (Row 1 can either be blank or contain headers). It also assumes that the cells you show as blank are truly empty (that is, Columns A through C do NOT contain formulas display "" for those blanks). If your data can meet those conditions, then place this normally entered formula in cell D2 and copy it down to the end of your data...

=IF(COUNTA(A2:C2)=3,0,N(D1)+1)

Hi Rick,

Thank you so much for your reply! You have explained your solution really well (I can understand it, which isn't usual for me LOL).

Unfortunately, I specifically wanted an array formula to avoid the primary issue I was encountering with the standard formula I had - adding new rows at the bottom of the sheet. I really don't want to be copy/pasting formula information (sidenote, google sheets for some reason won't even paste just formulas) - it'd be all too messy. That's why I'm changing everything over to array formulas.

 

[Rick Rothstein]

Unfortunately, I specifically wanted an array formula to avoid the primary issue I was encountering with the standard formula I had - adding new rows at the bottom of the sheet. I really don't want to be copy/pasting formula information (sidenote, google sheets for some reason won't even paste just formulas) - it'd be all too messy. That's why I'm changing everything over to array formulas.

The following array-entered formula has the same requirements as my original formula, but you can copy it down past the end of your existing data in anticipation of new data being entered at a later date. Note that I assumed the largest row number your data would ever exist in is Row 1000... if that assumption is wrong, then change the red highlighted 1000's to the actual largest row number you will ever need... then array-enter the formula in cell D2 and copy cell D2 down to that largest row number. Note that if 1000 is larger then your actual largest needed row number, change the 1000's to that number anyway... the smaller the largest specified row number, the more efficient the formula.

[table="width: 500"]
[tr]
	[td]=IF(COUNTA(A2:C2)=3,0,IF(ROW()>MAX(ROW(A$1:C$[B][COLOR="#FF0000"]1000[/COLOR][/B])*(A$1:C$[B][COLOR="#FF0000"]1000[/COLOR][/B]<>"")),"",N(D1)+1))[/td]
[/tr]
[/table]