Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Hi TWIMC
OK, I have the following test data in an Excel workbook and I want to be able in a pivot table to count the number of clients for each employee. A B C C1 20 EMPLOYEE1 C1 22 EMPLOYEE1 C2 4 EMPLOYEE1 C3 14 EMPLOYEE1 C3 11 EMPLOYEE1 C4 7 EMPLOYEE1 C5 9 EMPLOYEE1 C1 4 EMPLOYEE2 C1 2 EMPLOYEE2 C2 10 EMPLOYEE2 C3 8 EMPLOYEE2 C3 23 EMPLOYEE2 C4 15 EMPLOYEE2 C5 15 EMPLOYEE2 Currently the pivot table adds up the each row thus giving a total number of 7 clients for employee 1 but I want to see 5. Now I believe I'll need to create a new column to be included in the pivot table which I can then sum rather than count and I've found on numerous posting here and on other internet site the following formula, =SUM(IF(LEN(Sheet1!$A$2:$A$100)0,1/COUNTIF(Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100))), but I need to adapt this formula to only look at the range for each employee, so I think I need to use either SUBTOTAL or SUMPRODUCT, but not sure which or how. so any help would be much appreciated. TIA KM |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count and display unique values | Excel Worksheet Functions | |||
DSUM with Criteria containing non-hardcoded values? | Excel Worksheet Functions | |||
counting unique values and matching to quantities | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions |