Hi all,
This one may be hard to explain, but I'll do my best.
Via the table below.
I need to:
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
This one may be hard to explain, but I'll do my best.
Via the table below.
I need to:
- Sum hours
- Where Job # (c2:c9) = E2
- 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