Suppose you have a list of records:
+-----+-----+-----+
| | A | B |
+-----+-----+-----+
| 1 | 100 | x |
+-----+-----+-----+
| 2 | 50 | x |
+-----+-----+-----+
| 3 | 30 | y |
+-----+-----+-----+
| 4 | 180 | z |
+-----+-----+-----+
| 5 | 140 | k |
+-----+-----+-----+You want to get sums of all of them, grouped by the value in column B.
But you have an already predefined list of values, for which you want to have sums:
+-----+-------+-----+
| | C | D |
+-----+-------+-----+
| 1 | x | 150 | < =SUMIF(B1:B;C1;A1:A)
+-----+-------+-----+
| 2 | y | 30 | < =SUMIF(B1:B;C2;A1:A)
+-----+-------+-----+
| 3 | other | 320 | < =???
+-----+-------+-----+Getting the total sum for x and y is easy: you can use SUMIF(B1:B;C1;A1:A) in column D to get total for x and so on.
But how to get the total sum for all the records that are neither x nor y (in this case they're z and k) to print it in D3 cell?
1 Answer
The solution by Máté Juhász in the comments is an excellent method. But another way of using a NOT AND criteria in this situation, which may be applicable to other problems, is to use SUMPRODUCT or SUMIFS
For example:
=SUMPRODUCT((B:B<>C1)*(B:B<>C2)*A:A)
=SUMIFS(A:A,B:B,"<>"&C1,B:B,"<>"&C2)
