Sum based on range of multiple criteria....

[AnaChela]

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:

[TABLE="width: 117"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD] B[/TD]
[TD]C [/TD]
[TD] D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]bbb[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bbb[/TD]
[TD]1[/TD]
[TD]eee[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ccc[/TD]
[TD]1[/TD]
[TD]hhh[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]aaa[/TD]
[TD]1[/TD]
[TD]qqq[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]abc[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ccc[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]qqq[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]bbb[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]bbb[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]hhh[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]hhj[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]eee[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]eee[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[lrobbo314]

How about this?

=SUM(--ISNUMBER(MATCH($B$2:$B$14,$D$2:$D$5,0))*($C$2:$C$14))

 

[lrobbo314]

I just saw that you want to sum the numbers that don't have matches. That would be

=SUM(--NOT(ISNUMBER(MATCH(B2:B14,D2:D5,0))*(C2:C14)))

Both of these are also array formulas.

 

[lrobbo314]

Slight alteration.

=SUM(--ISNA(MATCH(B2:B14,D2:D5,0))*(C2:C14))

 

[AnaChela]

Slight alteration.

=SUM(--ISNA(MATCH(B2:B14,D2:D5,0))*(C2:C14))

It worked!! Here's the actual formula I used: {=ABS(SUM(--ISNA(MATCH(G7:G42,Categories!B1:B27,0))*(J7:J42)))}

I knew there was a better way, thanks for introducing me to ISNA!

 

[barry houdini]

{=ABS(SUM(--ISNA(MATCH(G7:G42,Categories!B1:B27,0))*(J7:J42)))}

In fact the -- aren't required here, this should work

=ABS(SUM(ISNA(MATCH(G7:G42,Categories!B1:B27,0))*(J7:J42)))

confirmed with CTRL+SHIFT+ENTER

or you can use SUMPRODUCT to avoid array entry, e.g.

=ABS(SUMPRODUCT(ISNA(MATCH(G7:G42,Categories!B1:B27,0))+0,J7:J42))