Google sheets, SUMIF function gives: "Argument must be a range" error if I use ADDRESS function as second parameter of range

0
votes

I am writing a formula in Google Sheets. Here's the formula;

=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), "<>0") > 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)))

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;

ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A)))

I was able to make this formula work for countif function but when I use the same formula in sumif function, it gives me Argument must be a range error.

This is the second version (the one giving an error)

=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), "<>0") > 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)))

I also tried to make ADDRESS function work independently, in a completely different cell and it gives the correct address.

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?

1
google-sheetsgoogle-sheets-formula
ADDRESS returns a string, not a range. You'd either have to wrap that in INDIRECT or use an INDEX formula like: C$3:INDEX(C2:C,COUNT(A2:A)) instead of C$3:ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))) - Rory
C$3:INDEX(C2:C,COUNT(A2:A)) worked thanks a lot. although does it return the number of entire C column or is it the last non empty cell? - Hakan Ali Yasdi
It returns a cell in column C based on how many cells in A have numbers in them. - Rory
alright got it. INDEX did the trick, formula is working now. thanks. - Hakan Ali Yasdi

1 Answers

2
votes

Replacing ADDRESS(ROW(INDEX(A2:A,COUNT(A2:A))) with C$3:INDEX(C2:C,COUNT(A2:A)) did the trick. Apparently ADDRESS returns a string rather than a reference.