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

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Hours[/TD]
[TD]Job #[/TD]
[TD]Code #[/TD]
[TD]Reference Job[/TD]
[TD]Exclude these codes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]job-1[/TD]
[TD]code-1[/TD]
[TD]job-2[/TD]
[TD]code-2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]job-2[/TD]
[TD]code-2[/TD]
[TD][/TD]
[TD]code-3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]job-3[/TD]
[TD]code-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]job-1[/TD]
[TD]code-3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]job-2[/TD]
[TD]code-3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5[/TD]
[TD]job-3[/TD]
[TD]code-4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5[/TD]
[TD]job-1[/TD]
[TD]code-4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5[/TD]
[TD]job-2[/TD]
[TD]code-5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.