lookup last value and deduct entries from another row

Can you run that last paragraph again? It is not clear what you want to calculate.

If you enter data into cell P22, then you can't have a formula in cell P22 at the same time.

Please manually mock up the expected result and explain the logic that leads to the result.

Can you run that last paragraph again? It is not clear what you want to calculate.

If you enter data into cell P22, then you can't have a formula in cell P22 at the same time.

Please manually mock up the expected result and explain the logic that leads to the result.

How do I create a formula that looks up the last value in row 21,

That is cell R21, right?

checks when the previous value occurred in row 21,

That is cell P21, right?

counts how many rows are in between the last and the second last occurrence,

Wait, what?? They are both on the same row.

adds the equivalent number of columns

Wait, what?? Which columns?

up in row 22 and deducts this from the last entry in row 21?

It does not make sense.

Still not clear. Where should the formula go? Can you please mock up the expected result and post a screenshot?

I don't understand. P21 now shows with 302k. What happened to 850k?

Look, I'm really trying to help you here, but you are being a bit cryptic. I asked you which cells should have the formulas. You did not answer that. If you don't reply to questions for clarification, then I can't help you.

If P21 contains the $850k, in which cell do you want to put the formula? You cant have a value and a formula in the same cell.

sorry I am not trying to annoy you nor being cryptic and sincerely appreciate your effort - the 850k (P21) is a computed value from another cell - in order not to make too complicated I left the input factors for that value out.

So it this specific case it would be P21-SUM(N22:P22) and R21-SUM(Q22:R22)

or generalised
first entry in row 21(assume letter X)-SUM(N22:X22)
second entry in row 21(assume letter AA)-SUM(Y22:AA22)
third entry in row 21 (assume letter BA)-SUM(AB22:BA22) and so on...

Do you have a problem reading what I write or are you ignoring my question ?

In which cell(s) or row(s) do you want to put the formula that uses the value from rows 21 and 22?

I don't think you understand what I'm asking.

A formula has to be entered into a cell. You've given me the formula logic. But which cell should I click on to enter the formula??

If a formula calculates values from P22 and P21 it cannot be entered into either cells P22 or P21. So, into which cell should the formula be entered?

I'm trying to help you here, but you are not doing much to let me do that.