mykal66
09-24-2015, 01:51 AM
Hi everyone
I have a spreadsheet with several columns one of which is staff member which is in column C. Column B then has a range of values associated with the staff member in column C. I need to be able to count unique values in column B associated with the staff member in Columns C. I have a formula that works manually when i fleeter the staff member in column C.
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B3,ROW(B3:B5000)-ROW(B3),,1)),
IF(B3:B5000<>"",MATCH("~"&B3:B5000,B3:B5000&"",0))),ROW(B3:B5000)-ROW(B3)+1),1))}
Does anyone know if i can automate the count on a separate sheet rather than a manual process please?
I've attached an example of what i need, the main data would be on sheet 1 with results on sheet 2. In the example workbook I've manually entered the values i would expect to see (which the formula above does return with the manual process)
Thanks as always in advance
Mykal
14447
I have a spreadsheet with several columns one of which is staff member which is in column C. Column B then has a range of values associated with the staff member in column C. I need to be able to count unique values in column B associated with the staff member in Columns C. I have a formula that works manually when i fleeter the staff member in column C.
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B3,ROW(B3:B5000)-ROW(B3),,1)),
IF(B3:B5000<>"",MATCH("~"&B3:B5000,B3:B5000&"",0))),ROW(B3:B5000)-ROW(B3)+1),1))}
Does anyone know if i can automate the count on a separate sheet rather than a manual process please?
I've attached an example of what i need, the main data would be on sheet 1 with results on sheet 2. In the example workbook I've manually entered the values i would expect to see (which the formula above does return with the manual process)
Thanks as always in advance
Mykal
14447