How to count unique non-0 values in one range based on categories of another range?

Dear all:

I've done a general forum search for "counting unique values two ranges" and haven't been able to come up with solution that helps my situation, and the built-in help for Excel 2007 regarding "how to count unique values using a combination of the MATCH, LEN, FREQUENCY and COUNTIF" functions only addresses operating in one range -- not to mention that I now have a headache trying to understand HOW the MATCH and LEN functions are supposed to work -- so I REALLY hope someone can help me here.

I'm trying to find a series of equations that will help me count unique values in one data range based on specifics in another data range: how many recipients in a specific department have received cash awards (need unique values, not duplicates).

I'm using Excel 2007 and have attached a dummy spreadsheet, but I'm using Courier New so I hope that the following data sample will show up legibly:

Name | Department | Amount | Award Date
----------------------------------------------------
Name01 | Dept.A | $25.00 | 2009-01-01
Name02 | Dept.A | $25.00 | 2009-01-01
Name03 | Dept.A | $50.00 | 2009-01-01
Name04 | Dept.A | $50.00 | 2009-01-01
Name05 | Dept.A | $50.00 | 2009-01-01
Name06 | Dept.B | $35.00 | 2009-01-15
Name07 | Dept.B | $35.00 | 2009-01-15
Name08 | Dept.B | $35.00 | 2009-01-15
Name09 | Dept.B | $50.00 | 2009-01-15
Name10 | Dept.B | $75.00 | 2009-01-15
Name11 | Dept.C | $75.00 | 2009-01-30
Name12 | Dept.C | $75.00 | 2009-01-30
Name13 | Dept.C | $50.00 | 2009-01-30
Name14 | Dept.D | $50.00 | 2009-02-05
Name15 | Dept.D | $50.00 | 2009-02-05
Name16 | Dept.D | $50.00 | 2009-02-05
Name17 | Dept.D | $50.00 | 2009-02-05
Name18 | Dept.D | $20.00 | 2009-02-05
Name19 | Dept.D | $20.00 | 2009-02-05
Name20 | Dept.D | $20.00 | 2009-02-05
Name02 | Dept.A | $10.00 | 2009-02-10
Name06 | Dept.B | $10.00 | 2009-02-10
Name13 | Dept.C | $10.00 | 2009-02-10
Name10 | Dept.B | $10.00 | 2009-02-15
Name14 | Dept.D | $10.00 | 2009-02-15
Name06 | Dept.B | $10.00 | 2009-02-25
Name13 | Dept.C | $10.00 | 2009-02-25
Name17 | Dept.D | $10.00 | 2009-02-25
Name18 | Dept.D | $50.00 | 2009-02-25
Name19 | Dept.D | $50.00 | 2009-02-25

So, if I do it manually using text filters, I can ascertain that:

1. Dept.A has 5x employees (Name01, Name02, Name03, Name04 and Name05);
2. Although 6x awards have been given to employees in Dept.A, there are only 5x unique recipients --> this is one type of key data that I need to capture;
3. The total amount awarded to Dept.A is $25.00 + $25.00 + $50.00 $50.00 + $50.00 + $10.00 = $210.00.

Another example:
1. Dept.B has 5x employees (Name06 ~ 10);
2. Although 8x awards have been given to employees in Dept.B, there are only 5x unique recipients;
3. The total amount awarded to Dept.B is $35.00 + $35.00 + $35.00 + $50.00 + $75.00 + $10.00 + $10.00 + $10.00 = $260.00.

And so on. Can you please help me figure out what equations I can use to count only the unique recipients (non-zero value) in the "NAME" column, but based on which department name I'm looking at in the "DEPARTMENT" column?

I think I've figured out the equation to find out how much was paid to specific department. For example, Dept.A's equation would be =SUMIF(C3:C32,G3,D3:D32).

Thank you in advance to anyone who can help me!
-T.Zukumori

Please do us all a favour and use the standard font in this forum. It's a heck of a lot easier on the eyes.

I think you could avoid Arrays altogether (given you use 2007) if you create one additional column... the below assumes Names are unique & distinct to a Dept (ie 1:1 relationship) post back if not the case

F3:
=IF(ISNA(MATCH($B3,$B$2:$B2,0)),IF(SUMIF($B$3:$B$32,$B3,$D$3:$D$32),10,1),0)
copy down to F32

What the above will do is set a value in F of one of: 0, 1, 10

0 where Name has appeared previously (duplicate)
1 where Name has never appeared previously yet no awards have been given
10 where Name has never appeared previously and awards have been given

You can then make use of XL2007's COUNTIFS formula (note: not available pre2007)

Count of Unique Names:
I5: =COUNTIFS($C$3:$C$32,$H3,$F$3:$F$32,">0")

Count of Unique Names Receiving Awards:
I6: =COUNTIFS($C$3:$C$32,$H3,$F$3:$F$32,10)

The above can be copied to the other Depts.