Sum column exclude duplicates

I've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(F2:F1079))

The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.

So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?

Maybe:

=SUM(INDEX(($T$1:$T$1079="SOUTH ATLANTIC") * ($H$1:$H$1079<>"WAL-MART STORES, INC") * (MATCH($F$1:$F$1079, $F$1:$F$1079,0) = ROW($F$1:$F$1079)), 0))

The range has to be exact, and there can't be any blanks in the F range.

Perhaps?

Please Login or Register  to view this content.

You don't need the F column if you are adding 1's for each match... just use 1 as the sum value if TRUE...

Confirmed with CTRL+SHIFT+ENTER

If you have other values in F, other than 1, to add up, then:

Please Login or Register  to view this content.

You need to confirm the formula with CTRL+SHIFT+ENTER keys not just ENTER. When you do that, you should see { } brackets appear around the formula...and hopefully no more error messages...

You can't use whole column references in 2003 for array formulas like this, so you need to just replace the 1079 with whatever bottom of the range you desire.

.. but be wary that array formulas don't like large ranges to much, the larger the range and the more array formulas you have the more inefficient and slow the processing becomes...

... so try to limit that range to an absolute cell range and not more than you will most likely every need.

Perhaps?

Please Login or Register  to view this content.

confirmed with CSE keys.

Try:

Please Login or Register  to view this content.

confirmed with CSE keys.

So what is the full requirement now?

Maybe?

Please Login or Register  to view this content.

confirmed with CSE

See Post # 17 above.