Apply formula only to visible cells after filtering

Hi,

How to apply formula only to the cells visible after filtering?
For eg : My sheet has 3 columns. Column A contains alphabets, Column B contains Words and column C blank (to be filled with data using formula). I use filter to select rows with column A = alphabet A and column B with word 'excel'. Assuming the rows 2,3,5,10,11 are visible. I hardcode a value 1 to C2. I need to fill 10(which is value of C2 i.e 1, + 9) to C3, 19 (value of C3 i.e 10, + 9) to C5, 28 (19 + 9) to C10, 37(28 + 9) to C11. Basically, my question is how to apply formula to only visible cells in column C?
Or
is there any other way to do this?

Thanks in advance.!

After filtering, put 1 in C2, and then put this formula in the next visible cell in column C (C3 in your example):

=MAX(C$1:C2)+9

Then you can copy that formula into the other visible cells in column C - it will only be put into the visible cells.

Hope this helps.

Pete

Thanks Pete,
It helped to solve it.

But if my visible rows are like this - 2, 5, 10, 11 (3 is not visiblen now), how should I change the formula which is to be included in C5 now?

Also if I need to leave C2 as it is, hardcode the value 1 to C5 and include a formula to fill data for the rest i.e. C10, C11, what should be the modifications?

Thanks in advance.

You put 1 in the first visible cell (let's say C5). Then in the next visible cell (say C10) you would enter this formula:

=MAX(C$1:C9)+9

and then copy that down. Note that the range is to the cell above it (which in this case is hidden by the filter).

You must ensure that column C is empty before applying the filter (i.e. no other values left over from previous filters).

Hope this helps.

Pete

Dear Pete,

yes. It helped.

Thanks a lot.!