# Excel: Avoid/Shorten multiple SUMIF?

I have a formula in my Excel sheet that looks like this:

``````1000 - SUMIF(\$B:\$B;"A1400";\$A:\$A) - SUMIF(\$B:\$B;"B1400";\$A:\$A) - SUMIF(\$B:\$B;"C1400";\$A:\$A) - ...
``````

And there are a lot more SUMIF that follow. Is there a way to avoid writing it like this, to condense the ~15 SUMIF statements? For example, can I tell Excel to draw the criteria (A1400, B1400, C1400) from a cell range or an array?

• you can add a helper column to mark if data need to be considered. (e.g. enter your criteria values to column X, then in Column C you can write `=COUNTIF(X:X;B2)`, and your complex formula will be `=1000-SUMIF(C:C;">0";A:A)`) May 28, 2019 at 13:22

You can use an array in the criteria if you wrap in SUMPRODUCT:

``````=1000 - SUMPRODUCT(SUMIF(\$B:\$B;{"A1400","B1400","C1400"};\$A:\$A))
``````

You can also put the list of criteria in a range and use that range. For example if the criteria was in Z1:Z3:

``````=1000 - SUMPRODUCT(SUMIF(\$B:\$B;Z1:Z3;\$A:\$A))
``````

In these cases there will be done 3 SUMIFS and the results will be added, then that result will be subtracted from `1000`

• criteria in the question is text, not range May 28, 2019 at 13:23
• Thank you. This should work with SUMIFS as well, right? So `SUMPRODUCT(SUMIFS(\$A:\$A;\$B:\$B;Z1:Z3;\$C:\$C;Y1:Y3))`? The intention is to sum if the criteria-pairs Z1/Y1, Z1/Y2, Z1/Y3, Z2/Y1 etc. are fulfilled.
– Alex
May 28, 2019 at 14:02
• @Alex you will need to transpose one of the ranges: `SUMPRODUCT(SUMIFS(\$A:\$A;\$B:\$B;Z1:Z3;\$C:\$C;Transpose(Y1:Y3)))` and it will only work with two, you cannot do a third. May 28, 2019 at 15:49
• @ScottCraner Makes sense, thanks again. Out of curiosity, what would you do if you wanted to add a third criterion? Would you have to use a different construct entirely?
– Alex
May 29, 2019 at 6:32
• You would need a different formula array type formula @alex May 29, 2019 at 13:46