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.