Nested functions IF and SEQUENCE. Is that possible?

Question

I have an excel with members. Once a year we send out an invoice. In column N it states the amount to be invoiced. Not all members should have an invoice, hence some N cells contain no amount. In column P I would like excel to create an invoice number starting with 24001 increasing with one and without gaps. Obviously no invoice number should be created if the amount in column N is zero.

I have tried using nested functions where the function SEQUENCE is used when the argument in the function IF is true, but that doesnt seem to work, or I can't figure out how to do it. I concider myself an below average excel user as I don't use functions as much as I should.

This is what I have tried without success: =IF(N2>0;(=SEQUENCE(204;1;24001;1));' ') but in a Swedish version.

sergeibaklan · Answer

hoki_kroki&nbsp;
As variant
=LET(
  range, C3:C12,
  number, SCAN(0, range, LAMBDA(a,v, a+ IF(v="", 0, 1) ) ),
  IF( range = "", "", number)
 )
for

Need to be adjusted for your actual data.

Forum Discussion

Nested functions IF and SEQUENCE. Is that possible?

1 Reply

Resources