Sum column based on conditions for subsums

user_13824985 0
Viewed 38

So I have a table which basically looks as follows:

Criterion  Value
1         -5
1          1
2          5
2          5
3          2
3         -1

I want to sum the values in column B based on the criteria in column A, but only if the sum for an individual criterion is not negative. So for example if I ask for the sum of all values where criterion is between 1 and 3, the result should be 11 (the values for criterion 1 not being included in the sum because they add up to a negative number.

My first idea was to add a third column with a sumif([criterion];[@criterion];[value]) and then use a sumifs function which checks whether that that third column is negative. However, my table has +100k lines and with that many sumif functions it becomes intolerably slow.

I know I could create a pivot table to the same effect, but that has two drawbacks: I would have to create a separate sheet, which would add complexity, and my table is frequently updated which means I would have to manually update that pivot table every time to allow for downstream calculations. NBD and I could do that as a last resort, but I wonder whether there isn't a more elegant way to solve this problem.

I would want to avoid VBA to avoid complexity (the sheet will be used by other persons).

Thank you

excel-formula excel
1 Answers

This can be easily done using UNIQUE() and the two versions of SUMIF() in this way:

  1. First collect all the criteria with =UNIQUE(A2:A7) -- Assuming your data are in columns A and B starting from row 2, this goes in cell C2, with "Criteria" in C1
  2. Compute the subtotals for all criteria using =SUMIF($A$2:$A$7, C2, $B$2:$B$7) -- This goes in cell D2 and extends as the criteria do, "Partials" in cell D1
  3. sum all the data in step 2 yielding a positive sum with =SUMIF(D2:D7, ">0") in cell E2

If you have a lot of data I suggest to use the column references to avoid absolute references and the need to adjust the formulas as data change (in number):

  1. The first formula becomes =UNIQUE(A:A) -- Don't care about the heading being taken (strings and empty cells are not summed)
  2. For the second formula use =SUMIF(A:A, C2, B:B)
  3. Use =SUMIF(D:D, ">0") for the last step

This should be reasonably fast, using just as many extra cells as the number of distinct criteria (multiplied by 2).

user_138214490
Related