Excel: Optimizing a bunch of very heavy SUMIFS

excel sumifs

abcde123483 · Dec 18, 2011 · Viewed 22.5k times · Source

I have some data that I want summed matching certain criteria

Example data:

  A    B   C   D
  Id   Id2 Id3 Val
  1    1   8   6
  1    2   7   7
  1    3   3   8
  1    4   6   4
  1    4   78  7
  1    1   2   9
  1    3   1   4
  1    4   3   6
  1    1   5   8
  1    4   7   2

Now I want the Val summed for every Id, based on certain criteria (e.g. Id2=4 and Id3=2) for every Id which has 100 values, but I want to avoid rerunning the sumifs for every Id since the table is heavy.

What I have been doing so far is to

= SUMIFS(D:D, A:A, "=1", B:B, "=4", C:C, "=2")
= SUMIFS(D:D, A:A, "=2", B:B, "=4", C:C, "=2")
= SUMIFS(D:D, A:A, "=3", B:B, "=4", C:C, "=2")
= SUMIFS(D:D, A:A, "=4", B:B, "=4", C:C, "=2") ...

(if I remember the syntax of sumifs correctly)

Is there a faster way that avoids rerunning the sumifs for each Id ?

Answer

If you want to figure lots of SUMs in this way, it's best done with a pivot table.

You may also try DSUM, but I doubt it's going to be any faster than SUMIF.

Excel: Optimizing a bunch of very heavy SUMIFS

Answer

Related questions