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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Why not use DataFilterAdvanced Filter to extract the unique combinations of
Cs and Employees, then build your Pivot table off the unique records? "Kevin McCartney" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Try something like =SUMPRODUCT(((A1:A8<"")/COUNTIF(A1:A8,A1:A8&""))*(C1:C8="employee1")) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=534492 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Unfortunately the records are unique in that I would expect to see a Cs total
for employee1 of 5 and B total summation of 87, if a use the Advance Filter is removes two of the records which is not want I want. Thanks any way "Duke Carey" wrote: Why not use DataFilterAdvanced Filter to extract the unique combinations of Cs and Employees, then build your Pivot table off the unique records? "Kevin McCartney" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
But you can extract the unique values to a new range, leaving the original
data intact. Then use the new range as the source for your pivot table. "Kevin McCartney" wrote: Unfortunately the records are unique in that I would expect to see a Cs total for employee1 of 5 and B total summation of 87, if a use the Advance Filter is removes two of the records which is not want I want. Thanks any way "Duke Carey" wrote: Why not use DataFilterAdvanced Filter to extract the unique combinations of Cs and Employees, then build your Pivot table off the unique records? "Kevin McCartney" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Hi Dav,
Unfortunately this doesn't help because the sample data is was just to show problem that I have, the real data contains several thousand rows so I can't set the data range individualy for each employee. Thanks any way ciao KM "Dav" wrote: Try something like =SUMPRODUCT(((A1:A8<"")/COUNTIF(A1:A8,A1:A8&""))*(C1:C8="employee1")) Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=534492 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
I've tried this but the summation of column B for employee1 does not equal
87, so it does not work, try it, you'll see. TIA ciao KM "Duke Carey" wrote: But you can extract the unique values to a new range, leaving the original data intact. Then use the new range as the source for your pivot table. "Kevin McCartney" wrote: Unfortunately the records are unique in that I would expect to see a Cs total for employee1 of 5 and B total summation of 87, if a use the Advance Filter is removes two of the records which is not want I want. Thanks any way "Duke Carey" wrote: Why not use DataFilterAdvanced Filter to extract the unique combinations of Cs and Employees, then build your Pivot table off the unique records? "Kevin McCartney" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER... =COUNT(1/FREQUENCY(IF(C$2:C$100=E2,MATCH(A$2:A$100,A$2:A$10 0,0)),ROW(A$2: A$100)-ROW(A$2)+1)) ....where E2 contains the employee of interest. Hope this helps! In article , Kevin McCartney wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Hi Domenic,
you are awarded the Gold star, you got it to work, thank you so very much, your talents are highly appreciated. Thanks and take care ciao KM "Domenic" wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =COUNT(1/FREQUENCY(IF(C$2:C$100=E2,MATCH(A$2:A$100,A$2:A$10 0,0)),ROW(A$2: A$100)-ROW(A$2)+1)) ....where E2 contains the employee of interest. Hope this helps! In article , Kevin McCartney wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Kevin,
Where do you insert this formula? Do you insert it into a new column on the pivot table? Or into the source data? Thanks! Mary Katherine |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values with criteria
Excel 2007 PivotTable
Modernized formula for source data: =1/COUNTIFS([Client],[Client],[Empl],[Empl]) Also shown: No formula method: http://www.mediafire.com/file/2itm5en1hmx/12_31_09.xlsx |
Reply |
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 |