Search code examples
excelsumifs

(MS Excel) How to automatically increment the row of a criteria in the sumifs function if it is enclosed in quotation marks?

This is the function or method:

=SUMIFS(sum_range,criteria_range1,criteria1,...)

This is my formula that I want to drag down:

=SUMIFS(DTR!AE$2:AE$1048576,DTR!B$2:B$1048576,">='Payroll - Extra'!A2",DTR!B$2:B$1048576,"<='Payroll - Extra'!B2",DTR!A$2:A$1048576,'Payroll - Extra'!C$1)

After dragging down, my goal is that it would look like this (look at the bold letters):

=SUMIFS(DTR!AE$2:AE$1048576,DTR!B$2:B$1048576,">='Payroll - Extra'!**A3**",DTR!B$2:B$1048576,"<='Payroll - Extra'!**B3**",DTR!A$2:A$1048576,'Payroll - Extra'!C$1)

The problem at hand is that if the criteria uses operators such as: >=, <=, >, <, <>, or =, the requirement is that you enclose is in quotation marks. The problem is, if you drag down the formula, anything inside the quotation marks will not auto-increment. This is a problem because I need it to auto-increment.

Solution

  • You can concatenate the cell reference with the operator, instead of enclosing all in quotes. So your formula might look like:

    =SUMIFS(DTR!AE$2:AE$1048576,DTR!B$2:B$1048576,">=" & 'Payroll - Extra'!A2,DTR!B$2:B$1048576,"<=" & 'Payroll - Extra'!B2,DTR!A$2:A$1048576,'Payroll - Extra'!C$1)