1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • count closest empty cells in a row

    From Xxer Xxes@21:1/5 to All on Tue Dec 1 12:51:12 2020
    Hi

    if in A1 is a value , b1 empty , c1 empty ,
    in d1 my formula i need must return value 2 ( it found
    2 empty cells column C to the left ) .

    My database i have to update every day , is from A11 to A82160 ,
    but never have the same edge , data can be for eg
    in row A11 to AB11 , A12:M12 , A13:AD12 ,
    row by row is randomly different ;

    is there a way to can have a formula ?

    the formula I will enter in the same column ,
    from CS11 to CS 82170 .many thanks

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Dec 2 09:16:25 2020
    Hi,

    Am Tue, 1 Dec 2020 12:51:12 -0800 (PST) schrieb Xxer Xxes:

    if in A1 is a value , b1 empty , c1 empty ,
    in d1 my formula i need must return value 2 ( it found
    2 empty cells column C to the left ) .

    My database i have to update every day , is from A11 to A82160 ,
    but never have the same edge , data can be for eg
    in row A11 to AB11 , A12:M12 , A13:AD12 ,
    row by row is randomly different ;

    is there a way to can have a formula ?

    the formula I will enter in the same column ,
    from CS11 to CS 82170 .many thanks

    try it in CS11 with =COUNTBLANK(OFFSET($A11,,,,SUMPRODUCT(MAX((A11:CR11<>"")*COLUMN(A:CR)))))


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Xxer Xxes@21:1/5 to All on Wed Dec 2 14:01:25 2020
    =COUNTBLANK(OFFSET($A11,,,,SUMPRODUCT(MAX((A11:CR11<>"")*COLUMN(A:CR)))))

    It works very well for me ; thank you very much for
    your assistence , Sir !

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Xxer Xxes@21:1/5 to All on Tue Dec 29 09:32:02 2020
    Thank you very much for your response , Sir ;
    it helps me , of course .

    I wish you all a very very good new Year .

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)