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

Question

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.

You can place the operator within quotation marks, and concatenate the worksheet/cell reference. — Ron Rosenfeld, Aug 31 '17 at 10:44
Can I see an example how to do that? Functions do not work inside quotation marks as well. Is it something like this: ">=" + CONCATENATE(Cell Reference) — Pherdindy, Aug 31 '17 at 10:47
Use the `operator: &`. Something like `">=" & 'Payroll - Extra'!A2` — Ron Rosenfeld, Aug 31 '17 at 10:50

score 2 · Accepted Answer · answered Aug 31 '17 at 10:54

2

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)

answered Aug 31 '17 at 10:54

Ron Rosenfeld

53,870
7
28
60

Thanks saved me a couple of minutes to hours of searching for a solution – Pherdindy Aug 31 '17 at 10:56
You're welcome. If you would mark my response as the Answer, I would appreciate it. – Ron Rosenfeld Aug 31 '17 at 12:26
@MarcSantos One other suggestion: Since you are using almost a full column in your range arguments, you might try using the full column `AE:AE` so long as there is nothing in the first row that might confuse the results. – Ron Rosenfeld Aug 31 '17 at 18:33

score 1 · Answer 2 · answered Aug 31 '17 at 11:04

1

This is why I always avoid using SUMIFS.

SUMPRODUCT can be used instead to do what you want.

Instead of doing this:

= SUMIFS(<sum range>,<criteria range 1>,"<criteria 1>",
                     <criteria range 2>,<criteria 2>,...)

Do this:

= SUMPRODUCT(<sum range>,(<criteria range 1><criteria 1>)+0,
                         (<criteria range 2>=<criteria 2>)+0,...)

So in your case, it should be:

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

Since A2 and B2 are no longer in quotes, they will auto-increment when you drag the formula down.

Just FYI to explain what is going on here: The expressions inside the parentheses create an array of TRUE's and FALSE's. The +0 at the end converts this array of TRUE's and FALSE's to an array of 1's and 0's. Then SUMPRODUCT multiplies each n'th element in each array together and then adds up all of these individual products, effectively only adding up the elements that meet a certain condition (or in this case, several conditions).

answered Aug 31 '17 at 11:04

ImaginaryHuman072889

4,953
7
19
51

Thanks i'll try this out as well. My spreadsheet has became slow because of many formulas which completely automated the payroll besides the input of time. Trying to find a more efficient way – Pherdindy Aug 31 '17 at 11:07
I just opened a blank excel workbook, renamed two of the sheets to "DTR" and "Payroll - Extra" and copied and pasted this formula into a cell and it returned the value of "0", so it works for me. Either you are typing it in incorrectly, or possibly your sheet just has too much data for the formula to handle (you have over 1 million rows). – ImaginaryHuman072889 Aug 31 '17 at 11:59
I don't know how to use sumproduct properly as well. I just simply copy pasted the thing you typed in. I'll be learning how to use it first since I came across a matrix I need to use sumproduct on because sumifs can only sum a range of one column. – Pherdindy Aug 31 '17 at 12:14
I'm not sure why it isn't working for you. I copied and pasted the formula into an excel worksheet and it didn't give me an error. The only thing I'm thinking is perhaps if your sum range contains something other than numbers, then the formula may return an error. – ImaginaryHuman072889 Aug 31 '17 at 12:40
2

`SUMIFS` (and the other related `...IFS` formulas should be more efficient than `SUMPRODUCT`. This relates, at least in part, to the fact that `SUMPRODUCT` will always evaluate the entire expression; whereas the `...IFS` formulas will stop after the first "non-true" condition. The more complicated the expression, the more this difference will show up. (There are instances where you just can't use `SUMIFS`, though) – Ron Rosenfeld Aug 31 '17 at 13:15
1

Thanks Ron. I've always found `SUMPRODUCT` to be one of the more "reliable" and versatile excel functions, but didn't know that it was also less efficient. I'll have to keep this in mind for larger spreadsheets. – ImaginaryHuman072889 Aug 31 '17 at 13:27

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

2 Answers2