I have posted this query on Ozgrid, but not having much success so MR Excel please rescue me!!
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry out 3 checks on one customer number, which would result in the staff ref number being entered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number entered in the adjacent column.
The checks are spread across several worksheets - one for "full check", one for "Percenatge Check" etc....
The reply received on Ozgrid was to use -
=SUM(--(FREQUENCY(IF(B1:B100="Bill",MATCH(A1:A100,A1:A100,0)),ROW(INDIRECT("1:"&ROWS(A1:A100))))>0))
where the staff member was called Bill - comitted with CTRL & Shift & Enter
this works on one worksheet, but I do not know how to amend it to work across all the worksheets and give me a total.
thanks for aking the time to read this
xx
All the cust numbers are unique.
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry out 3 checks on one customer number, which would result in the staff ref number being entered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number entered in the adjacent column.
The checks are spread across several worksheets - one for "full check", one for "Percenatge Check" etc....
The reply received on Ozgrid was to use -
=SUM(--(FREQUENCY(IF(B1:B100="Bill",MATCH(A1:A100,A1:A100,0)),ROW(INDIRECT("1:"&ROWS(A1:A100))))>0))
where the staff member was called Bill - comitted with CTRL & Shift & Enter
this works on one worksheet, but I do not know how to amend it to work across all the worksheets and give me a total.
thanks for aking the time to read this
xx
All the cust numbers are unique.