All geek questions in one place

Excel: optimizing a group of very heavy SUMIFS

I have some data that I want to summarize, matching certain criteria

Sample data:

ABCD 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 Val to be summed for each Id based on specific criteria (for example, Id2 = 4 and Id3 = 2) for each Id that has 100 values, but I want to avoid re-running sumifs for each Id , since the table is heavy.

What I have done so far is

 = 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 sumifs syntax correctly)

Is there a faster way to avoid restarting sumifs for each Id ?

+4
source share
2 answers

If you want to portray a lot of SUM in this way, it is best to do this using a pivot table .

You can also try DSUM , but I doubt it will happen faster than SUMIF.

+3
source

You can also make an array formula that should also be lightning fast.

So, select the range in which you want to get the results, then press F2 to enter where you want to use the first formula. Then enter the following array formula:

 = SUMIFS(D:D, A:A, "="&{1;2;3;4}, B:B, "=4", C:C, "=2")

or

 = SUMIFS(D:D, A:A, "="&G1:G4, B:B, "=4", C:C, "=2")

where G1 - G4 have the values ​​you want to iterate over.

This will lead to the fact that it will capture data only once, and not several times, and will return all values ​​once, and not several times.

If you want to iterate from left to right, use comma instead of semicolon in your array.

When you enter an array formula, enter it as follows:

 Ctrl+Shift+Enter
+2
source

Source: https://habr.com/ru/post/1386955/

More articles:


All Articles