# Count Number Of Rows With Unique Entries In 2 Columns

#### weaholt

##### New Member
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### hatman

##### Well-known Member
Why wouldn't you simply enter the formula once in each sheet, then sum the results across all sheets... you could even perform the last step using a 3-D reference...

#### weaholt

##### New Member
I tried summing the formulas, but realised that if I have a staff check for Bill on sheet1 relating to customer A, and a staff check for Bill on sheet2 relating to the same customer, it calculated it as 2 checks when I want it to show just 1 check as it is for the same customer.

....I am not familiar with a 3D reference!

x

#### hatman

##### Well-known Member
Good point... and a 3-D reference wouldn't help you out of that problem.

The problem is that you can't span sheets with an array formula. How many rows of data are you searching through in all sheets? Will this number change over time?

And one last piece of advice: if you are going to cross-post, here is an article with the etiquette that should be followed.

#### weaholt

##### New Member
OOP! Sorry - I did mention that I had cross posted but didn't include the link - which is - http://www.ozgrid.com/forum/showthread.php?t=58409 - apologies again for this slip up.

I would normally wait a lot longer for a reply, but I'm on a tight deadline and under a bit of pressure. Rest assured I won't do it again.

In answer to your questions about my spreadsheet - Each worksheet has a different number of rows - although I could make these uniform if necessary. The number of rows could change over time - although I suppose I could add on X number of rows to ensure that whoever is completing it will never reach the end....would that help?

thanks so much for your time!
x

#### hatman

##### Well-known Member
here is what I would do:

In a new sheet, call it BTC (Behind The Curtains) enter the following formula in cell A1:
Code:
``=IF(ROW()>COUNTA('full check'!A:A),IF(ROW()>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A),IF(ROW()>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A)+COUNTA('percentage check'!A:A),"",INDEX('percentage check'!A:A,ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A))),INDEX('quality check'!A:A,ROW()-COUNTA('full check'!A:A))),INDEX('full check'!A:A,ROW()))``
Copy it into cell b1, then copy it down past the end of your data. This will concatenate all of your data into one sheet, and dynamically pick up any new entries in any of the three sheets. Then use your original formula to do your check in the BTC sheet only...

I would also write a formula on sheet BTC that says:
Code:
``=COUNTA(A:A)>COUNTA('full check'!A:A)+COUNTA('quality check'!A:A)+COUNTA('percentage check'!A:A)``
, and if it becomes false either a) Provide a warning to the user to copy down the formulas in sheet BTC, or b) A piece of VBA code that does the copy paste automatically.

Anyway... it's ONE solution, and NOT  necessarily the best.

#### beccy

##### Board Regular
did that formula give the desired result?

#### fairwinds

##### MrExcel MVP
Hi,

If your customer numbers are true numbers you could use something like:

=COUNT(1/FREQUENCY(IF(CHOOSE({1,2,3},Sheet1!B1:B10,Sheet2!B1:B10,Sheet3!B1:B10)="Bill",CHOOSE({1,2,3},Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)),CHOOSE({1,2,3},Sheet1!A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)))

Confirmed with Ctrl + shift + enter.

#### weaholt

##### New Member
oooh - that looks complicated!

I'm just trying to get me head round it - in the formula for the BTC sheet why are some of the worksheets mentioned more than others? Sorry if it's a thick quesiton - I'm trying to learn!!! I can usually see a pattern within the formula, but I'm stumped at this one!
x

#### hatman

##### Well-known Member
Make sure you give Fairwinds suggestion a review too: his looks like it will operate without the need to make a dynamic copy of the data, which would keep your file size and processor load to a minimum... my solution is pretty taxing, but effective.

As far as an explanation of my formula:

It's a dynamic data concatenation. It has 3 nested If() functions corresponding to your 3 sheets. From the ouside in:

1) Is the current row number greater than the total rows of data on the first sheet? If no, then extract data from the first sheet: INDEX('full check'!A:A,ROW()) where ROW() is the row number to get data from the first sheet. If YES then
2) Is the current row number greater than the total rows of data on the first 2 sheets? In no, then extract data from the second sheet INDEX('quality check'!A:A,ROW()-COUNTA('full check'!A:A)) where ROW()-COUNTA('full check'!A:A) is the row number to get data from on the second sheet. If YES then:
3) Is the current row number greater than the total rows of data on all three sheets? If YES, then return a logical blank. If NO then extract data from the third sheet: INDEX('percentage check'!A:A,ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A)) where ROW()-COUNTA('full check'!A:A)-COUNTA('quality check'!A:A) is the row number to extract data on the third sheet. Here is a breakdown into to multiple components to help illustrate:

Replies
3
Views
300
Replies
1
Views
244
Replies
2
Views
1K
Replies
3
Views
282
Replies
7
Views
912

1,171,588
Messages
5,876,320
Members
433,193
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back