There's got to be a better way!
In the example below, I want to sum column C only if none of values in column D appear in column B:
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
I can do it with an array:
{=SUM((C2:C14)*(B2:B14<>D2)*(B2:B14<>D3)*(B2:B14<>D4)*(B2:B14<>D5))}
But in my real spreadsheet, column D will have 25 or more values. This feels like a very messy way to get what I'm looking for. Does anyone have an easier, cleaner way to do this?
In the example below, I want to sum column C only if none of values in column D appear in column B:
B | C | D | |
2 | aaa | 1 | bbb |
3 | bbb | 1 | eee |
4 | ccc | 1 | hhh |
5 | aaa | 1 | qqq |
6 | abc | 1 | |
7 | ccc | 1 | |
8 | qqq | 1 | |
9 | bbb | 1 | |
10 | bbb | 1 | |
11 | hhh | 1 | |
12 | hhj | 1 | |
13 | eee | 1 | |
14 | eee | 1 |
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
I can do it with an array:
{=SUM((C2:C14)*(B2:B14<>D2)*(B2:B14<>D3)*(B2:B14<>D4)*(B2:B14<>D5))}
But in my real spreadsheet, column D will have 25 or more values. This feels like a very messy way to get what I'm looking for. Does anyone have an easier, cleaner way to do this?