Omit cells based on variable row number

**Steffen_DK** · 02-06-2012, 04:56 PM

The whole problem is discribed in the file along with an example.

Omit certain cells.xlsx

**Steffen_DK** · 02-07-2012, 07:03 AM

Bump no response

**Marcol** · 02-07-2012, 08:23 AM

Not to clear as to what you are aiming for.

Try this in M3

=COUNTIF(INDEX(A:A,$J$2,1):INDEX(INDEX(A:A,$J$2,1):INDEX(A:A,$J$3,1),MATCH(99^99,INDEX(A:A,$J$2,1):INDEX(A:A,$J$3,1),1)-1,1),$L3)

Drag Across to Column Q, then down as required.

Does that do as you wish?

**Steffen_DK** · 02-07-2012, 09:07 AM

It's almost perfect.
Except, when choosing Endrow=40, the last numbers in each column should be included: 8, 2, 9, 10 and 4. As long as Endrow is on or beyond an #N/A, the number preceeding that cell should be included.

Or if Endrow= 38, then 8, 2 and 9 (from A, B, C) should be included, but not 10 and 4 (from D, E)

Though if this is much complicated, I think it will be possible to change all my #N/A to 0s, that seems to do the trick too. However I'll have to change the function that creates the "Source" values.

**Marcol** · 02-07-2012, 09:52 AM

Or maybe try this

=COUNTIF(INDEX(A:A,$J$2,1):INDEX(INDEX(A:A,$J$2,1):INDEX(A:A,$J$3,1),MAX(MATCH(99^99,INDEX(A:A,$J$2,1):INDEX(A:A,$J$3,1),1),MATCH(REPT("z",255),INDEX(A:A,$J$2,1):INDEX(A:A,$J$3,1),1))-1,1),$L3)

[Edit]
On second thoughts, that won't work.
Easiest to return 0 (zero) instead of #N/A if you can.

**Steffen_DK** · 02-07-2012, 05:46 PM

Ok, I added

IFERROR(... ;0)

to all the source formulas. Took my pc 8 minutes for the 41000 cells, but it was well worth it, and getting rid of the N/A also helped on some other formulas that had trouble with those

Thank you!