-1

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.
hochl
  • 12,524
  • 10
  • 53
  • 87

1 Answers1

2

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

trincot
  • 317,000
  • 35
  • 244
  • 286
  • +1 thank's that solves another problem with the spreadsheet too! :) I knew that there must be a simple solution to that. – hochl Oct 28 '17 at 19:59