Search code examples
excelspreadsheetlibreoffice-calc

Spreadsheet Formula to Sum Values Over A if B is not in a List of Values

I have a table that looks the following:

|  A   | B | C |
| 40   | 1 | 1 |
| 180  | 2 | 2 |
| 34   | 1 |
| 2345 | 3 |
| 23   | 1 |
| 1    | 2 |
| 4354 | 3 |
| 2    | 2 |
| 343  | 4 |
| 2    | 2 |
| 45   | 1 |
| 23   | 1 |
| 4556 | 3 |

I want to get the sum of all fields in A where B is neither 1 nor 2 or any other value from colum C. This column contains the values of B where values from A should not be considered for the sum.

I do not know which values B might contain, those values are random and could grow larger, I just wanted to make the example small. My current solution is

{=SUMIF(B1:B13,C1:C2,A1:A13)}

so i can set the lines that should be excluded from the sum in column C. Unfortunately, the current solution does not solve my problem but something different -- it sums up the corresponding entries by value in C. My preferred solution would look something like

=SUMIF(B1:B13,"<>{1, 2}",A1:A13)
=SUMIF(B1:B13,"<>"&C1:C2,A1:A13)

if that were possible (it isn't). I would like to have:

  • a field (with a list, for example) or column where i can put in the values of B that I do not want to be part of the sum over A.
  • a method that works with Open Office as well as Excel. I prefer an OO solution.
Solution

  • You could use an array formula so that you can multiply each value in A with a condition. That condition can be any valid Excel formula, so, for instance, you could use MATCH to test if the B value occurs in C:

    =SUM((A1:A13)*ISNA(MATCH(B1:B13,$C:$C,0)))

    The ISNA function returns TRUE when the match fails, which in a multiplication is used as a numerical value 1. FALSE will make the product 0.

    Make sure to enter this as an array formula with Ctrl+Shift+Enter