Function NewOrProlonged(ContragentColNum As Integer, SquareColNum As Integer)
'NewOrProlonged = Application.WorksheetFunction.SumProduct((Range(Cells(7, ContragentColNum), Cells(EntireRow.Row, ContragentColNum)) = Cells(EntireRow.Row, ContragentColNum)) * (Range(Cells(7, SquareColNum), Cells(EntireRow.Row, SquareColNum)) = Cells(EntireRow.Row, SquareColNum)))
Dim rngContragent$, rngSquare$, crtContragent$, crtSquare$
       With Application.Caller
           crtContragent = Cells(.Row, ContragentColNum).Address
           crtSquare = Cells(.Row, SquareColNum).Address
           rngContragent = Range(Cells(8, ContragentColNum), Cells(.Row - 1, _
               ContragentColNum)).Address
           rngSquare = Range(Cells(8, SquareColNum), Cells(.Row - 1, _
               SquareColNum)).Address
       End With
       NewOrProlonged = Evaluate("SumProduct((" & rngContragent & " = " & _
           crtContragent & ")*(" & rngSquare & "=" & crtSquare & "))")
       'NewOrProlonged = Evaluate("SumProduct((" & rngContragent & "=" & crtContragent _
           & ")*(" & rngContragent & "<>"""")*(" & rngContragent & ">0)*(" & rngSquare & _
           "=" & crtSquare & ")*(" & rngSquare & "<>"""")*(" & rngSquare & ">0))")
End Function

=-ЕСЛИОШИБКА(-C2;СУММ(-(ПСТР(C2;СТОЛБЕЦ(1:1);1)="*")))

Function cnt%(str$)
       Dim arr As Variant
       If IsNumeric(str) Then
           cnt = str * 1
       Else
           arr = Split(str, "*")
           cnt = UBound(arr)
       End If
End Function

Sub PageBreak()
      Application.ScreenUpdating = 0: Application.EnableEvents = 0
      Dim F&, L&, I&, J&, K&, S$
        
      With ActiveSheet.UsedRange
          F = .Column
          L = .Column + .Columns.Count - 1
      End With
        
      Do While I < ActiveSheet.HPageBreaks.Count
          I = I + 1
          J = ActiveSheet.HPageBreaks(I).Location.Row
          For K = F To L
              If Cells(J, K).MergeCells And Cells(J - 1, K).MergeCells Then
                  If Cells(J, K).MergeArea.Address = Cells(J - 1, K).MergeArea.Address _
                  And S <> Cells(J, K).MergeArea.Address Then
                      S = Cells(J, K).MergeArea.Address
                      Set ActiveSheet.HPageBreaks(I).Location = Cells(J, K).MergeArea.Offset(1)
                  End If
              End If
          Next
      Loop
      Application.ScreenUpdating = 1: Application.EnableEvents = 1
End Sub

{=ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА()-1;{1;2;3});"")}

СМЕЩ(Лист1!C3;;;СЧЁТЗ(Лист1!C:C)-1)

=СУММ(ЕСЛИ(ЕОШИБКА(ПРОСМОТР(диапазон;значение));1))+1

=ПОИСКПОЗ(значение;диапазон);0)

Private Sub CommandButton1_Click()
Application.ScreenUpdating = 0: Application.EnableEvents = 0
Dim Rng, ResRng As Range
     If CommandButton1.Caption = "Скрыть пустые столбцы" Then
         Set Rng = Range("F5:J5").Find(Empty, LookIn:=xlValues)
         If Not Rng Is Nothing Then
             Set ResRng = Rng
             firstAddress = Rng.Address
             Do
                 Set Rng = Range("F5:J5").FindNext(Rng)
                 Set ResRng = Union(ResRng, Rng)
             Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
             ResRng.EntireColumn.Hidden = 1
         End If
         Set Rng = Range("L6:L19").Find(Empty, LookIn:=xlValues)
         If Not Rng Is Nothing Then
             Set ResRng = Rng
             firstAddress = Rng.Address
             Do
                 Set Rng = Range("L6:L19").FindNext(Rng)
                 Set ResRng = Union(ResRng, Rng)
             Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
             ResRng.EntireRow.Hidden = 1
         End If
         CommandButton1.Caption = "Отразить все столбцы"
     Else
         Range("F5:J5").Columns.Hidden = False
         Range("L6:L19").Rows.Hidden = False
         CommandButton1.Caption = "Скрыть пустые столбцы"
     End If
Application.ScreenUpdating = 1: Application.EnableEvents = 1
End Sub

=СМЕЩ(ИНДЕКС(B:B;ПОИСКПОЗ(C1;A:A;0));0;0;СЧЁТЕСЛИ(A:A;C1))

=F1*СУММ((F1>={0;0.15;0.25;0.5;0.75;1;1.5;3;4;6;8;12;18;24;30;36;45;52.5;60;70;80;100;120;140;160;180;200;220})*(F1<{0.15;0.25;0.5;0.75;1;1.5;3;4;6;8;12;18;24;30;36;45;52.5;60;70;80;100;120;140;160;180;200;220;" "})*{33.75;29.25;27.3;20.22;16.65;12.69;11.88;10.98;8.77;7.07;6.15;4.76;4.13;3.52;3.06;2.62;2.33;2.01;1.68;1.56;1.22;1.04;0.9;0.8;0.73;0.66;0.61;0.58})/100*3.731

=F1*СУММ((F1>=min)*(F1<max)*Ki)/100*3.731

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"*";""))

=СУММ(--(ПСТР(A1;СТОЛБЕЦ(1:1);1)="*"))