Google-sheets – Error using “sumif” together with “address” function

google sheets

Please see the data below:

Data

What I want to do is to write a formula in column D to sum up the order quantity (column C) starting from the row number specified in column F until current row, for the same item number.

However, when I use the address function nested in the SUMIF function, it gives an error (if I replace the address function with a value then it works).

So can someone please kindly advise me how I should write the formula in column D instead?

Best Answer

The problem is that address returns a string, so you need to turn that string into a reference.

Try this:

=sumif(indirect(address(F2,G2,2)&":"&address(ROW(B2),COLUMN(B2))),B2,indirect(address(F2,H2,2)&":"&address(ROW(C2),COLUMN(C2))))
Related Topic