Forum Discussion

hoki_kroki's avatar
hoki_kroki
Copper Contributor
Dec 21, 2023

Nested functions IF and SEQUENCE. Is that possible?

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.

 

Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 21, 2023

    hoki_kroki 

    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.

Resources