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:

	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?

 

[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))