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
Bookmarks