2

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?

Improve this question
1
  • 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))
    – Máté Juhász
    May 28, 2019 at 13:22

1 Answer 1

Reset to default
2

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

Improve this answer
5
  • criteria in the question is text, not range
    – Máté Juhász
    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.
    – Scott Craner
    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
    – Scott Craner
    May 29, 2019 at 13:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .