SUMIF while Excluding the values found within a range of cells

[dbarbella]

Hi all,
This one may be hard to explain, but I'll do my best.

Via the table below.
I need to:

1. Sum hours
2. Where Job # (c2:c9) = E2
3. Except When Code # (d2:d9) = any value listed in "Exclude these code" (f2:f9)

Answer would be 5, as only B9 is going to meet the criteria

I know I can use a <> as part of the criteria in this example because I am only excluding 2 values. but the real example can have many values, and I would like for users to be able to enter values into a column and have the formula automatically pull from that column and exclude whatever values are in there.

For a variety of reasons, need to do this as a formula, and NOT VBA

So the unique thing here, is specifying a range of cells to use for a <> exclusion criteria, instead of putting each value manually into the formula as a bunch of <>'s

A	B	C	D	E	F
1	Hours	Job #	Code #	Reference Job	Exclude these codes
2	5	job-1	code-1	job-2	code-2
3	5	job-2	code-2		code-3
4	5	job-3	code-2
5	5	job-1	code-3
6	5	job-2	code-3
7	5	job-3	code-4
8	5	job-1	code-4
9	5	job-2	code-5

<tbody>
</tbody>

Is there hope in solving this challenge?
Crossing fingers :>

Thanks all
-Dave

 

[Jonmo1]

Try

=SUMPRODUCT(--(C2:C9=E2),--ISNA(MATCH(D2:D9,F2:F3,0)),B2:B9)

 

[dbarbella]

Via a quick test, it works like a charm. Thank you so much for the fast solution :>

 

[Jonmo1]

You're welcome.