title: VBA Return Application.Sumifs answer in a variable
body: <p>Is it possible to store the answer of a function, written as follows, in a variable?</p>
  <pre><code>'--------------------
  'This is part of a somewhat bigger code so some variables has been declared again
  '--------------------
  
  Sub afletteren()
  
  Dim vAfletteren         As Boolean
  Dim OutWb               As Workbook
  Dim OutFileStatus       As Range
  Dim OutFileHeader       As Range
  Dim Key1                As Variant
  Dim Key2                As Variant
  Dim OutFileKPL          As Range
  Dim OutFileGBR          As Range
  Dim OutFileBedrag       As Range
  Dim OutFileStatistieknr As Range
  Dim OutFileVouchernr    As Range
  
  vAfletteren = True
  Set OutWb = ActiveWorkbook
  Set OutFileHeader = Range(&quot;D5&quot;)
  
  If vAfletteren = True Then
  
  With OutWb.Sheets(&quot;Dump&quot;)
  Set OutFileKPL = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;KPL&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  Set OutFileGBR = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;Rekeningnummer&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  Set OutFileBedrag = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;BedragPrimair&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  Set OutFileStatistieknr = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;Statistieknummer1&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  Set OutFileVouchernr = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;FactVerplNr&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  Set OutFileStatus = .Cells(OutFileHeader.Row, 1).EntireRow.Find(What:=&quot;Status&quot;, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
  End With
  
  'Whenever key1 = 0 then set the &quot;Status&quot; to &quot;Afletteren&quot;, the first row of this example equals to 0 so after running this code, the outcome has to be &quot;Afletteren&quot; in the first cell of the status column
  S = OutFileHeader.Row + 1
  
  With OutWb.Sheets(&quot;Dump&quot;)
  For T = OutFileHeader.Row + 1 To .Cells(Rows.Count, 1).End(xlUp).Row
      If .Cells(S, OutFileStatus.Column).Value &lt;&gt; &quot;Afletteren&quot; Then
           Key1 = Application.SumIfs(OutFileBedrag.EntireColumn.Address, OutFileKPL.EntireColumn.Address, .Range(Split(Cells(1, OutFileKPL.Column).Address, &quot;$&quot;)(1) &amp; S).Value, OutFileGBR.EntireColumn.Address, .Range(Split(Cells(1, OutFileGBR.Column).Address, &quot;$&quot;)(1) &amp; S).Value, OutFileVouchernr.EntireColumn.Address, .Range(Split(Cells(1, OutFileVouchernr.Column).Address, &quot;$&quot;)(1) &amp; S).Value)
          'Key1 = Application.SumIfs(sumrange                          , Range 1                        , Criteria 1                                                          , Range 2                        , Criteria 2                                                          , Range 3                              , Criteria 3
              
  'Check
  MsgBox &quot;Application.SumIfs(&quot; &amp; OutFileBedrag.EntireColumn.Address &amp; &quot;, &quot; &amp; OutFileKPL.EntireColumn.Address &amp; &quot;, &quot; &amp; .Range(Split(Cells(1, OutFileKPL.Column).Address, &quot;$&quot;)(1) &amp; S).Value &amp; &quot;, &quot; &amp; OutFileGBR.EntireColumn.Address &amp; &quot;, &quot; &amp; .Range(Split(Cells(1, OutFileGBR.Column).Address, &quot;$&quot;)(1) &amp; S).Value &amp; &quot;, &quot; &amp; OutFileVouchernr.EntireColumn.Address &amp; &quot;, &quot; &amp; .Range(Split(Cells(1, OutFileVouchernr.Column).Address, &quot;$&quot;)(1) &amp; S).Value &amp; &quot;)&quot;
  MsgBox Key1         'has to be equal to 0
  
              If Key1 = 0 Then
              .Cells(S, OutFileStatus.Column).Value = &quot;Afletteren&quot;
              End If
      End If
      S = S + 1
  Next T
  End With
  
  End If
  
  Exit Sub
  
  ERROR_HANDLING:
  MsgBox &quot;Error Handling = &quot; &amp; vStatus
  
  End Sub
  </code></pre>
  <p>Whenever i run this piece of code, it will return with a type-error on the msgbox line. is it possible to store the answer of the function in a variable instead of in a predefined range? i want to continue whenever the Sumifs function returns a value of 0.</p>
  <p>i hope to hear from someone.</p>
  <p>Thanks in advance,</p>