title: Google sheets, SUMIF function gives: "Argument must be a range" error if I use ADDRESS function as second parameter of range
body: <p>I am writing a formula in Google Sheets. Here's the formula;</p>
  
  <pre><code>=IF(COUNTIFS(C$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),3), C3, E$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),5), E3, F$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),6), "&lt;&gt;0") &gt; 1, "MULTIPLE POSITIVE LBS THAT'S GREATER THAN ZERO", MINUS(F3, SUMIFS(G$3:G$480, C$3:C$480, C3, E$3:E$480, E3))) 
  </code></pre>
  
  <p>Now the formula works like this but it only sums the values between row 3 and 480. What I want to do is get all the values between row 3 and the last non-empty field. In order to achieve that I used this formula;</p>
  
  <pre><code>ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A)))
  </code></pre>
  
  <p>I was able to make this formula work for <code>countif</code> function but when I use the same formula in <code>sumif</code> function, it gives me <strong>Argument must be a range</strong> error. </p>
  
  <p>This is the second version (the one giving an error)</p>
  
  <pre><code>=IF(COUNTIFS(C$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),3), C3, E$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),5), E3, F$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),6), "&lt;&gt;0") &gt; 1, "MULTIPLE POSITIVE LBS THAT'S GREATER THAN ZERO", MINUS(F3, SUMIFS(G$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),7), C$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),3), C3, E$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))),5), E3)))  
  </code></pre>
  
  <p>I also tried to make <code>ADDRESS</code> function work independently, in a completely different cell and it gives the correct address.</p>
  
  <p>This is the first time I'm writing sheets or any kind of excel formula so I couldn't find the source of the problem. What am I doing wrong?</p>