# COUNTIF help, or is there a better alternative?

#### vulture49

##### New Member
Hi everyone, needing help with my project.

I need to count the ordinal number (1st, 2nd, 3rd, etc) of an entry in a given list. For example:

 A B 1 Smith, John 1 2 Doe, Jane 1 3 Smith, John 2 4 Parker, Peter 1 5 Parker, Peter 2 6 Doe, Jane 2 7 Parker, Peter 3 8 Smith, John 3 9 Doe, Jane 3 10 Doe, Jane 4 11 Parker, Peter 4 12 Smith, John 4 13 Parker, Peter 5

<tbody>
</tbody>

Meaning, Parker, Peter - 5 means it's the 5th "Parker, Peter" entry in this table.

Now, the formula I used (for cell B1) is:
=COUNTIF(A\$1:A1,A1)

to be dragged down to all cells in column B.

This works perfectly well. But the thing is, I'm doing this for data with more than 5000 entries, and growing. The data in column A are entered through a barcode scanner. A new entry is entered in column A every time a barcode is scanned. Say we're already in 1000th entry. Excel processes a long time (around 3-5 seconds) before you can enter/scan the 1001st barcode. Why is this so? Is there a better way?

You might want to know:
The whole thing is the data source of a PivotTable wherein I only have to count the 1st entry of each unique item in column A. (i.e. only the "1" entries in column B to be counted). Regardless of how many "Parker, Peter"'s there are on the list, the fact that there is one instance of "Parker, Peter" in the table, shall be reflected in the PivotTable as "1".

The whole purpose of this project is to monitor the manpower entering the building. Here's the flow:
Person X scans Barcode ID every entry/exit made into the building (hence creating one new row in the table). Person X can go in and out of the building multiple times. Report shall say that Person X entered the building at least once.

That's the whole point of the COUNTIF. Because if I don't put any counter of some sort, every time Person X will scan the barcode ID will be counted as another entry, which will defeat the purpose of knowing "How many unique people entered the building?"

Please do help me with your suggestions! Sorry if I can't explain myself using a few words. Cheers!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

If you just wanted to count the unique values in column A you could try this array formula:

=SUM(IF(FREQUENCY(IF(LEN(OFFSET(A1,0,0,COUNTA(A:A),1))>0,MATCH(OFFSET(A1,0,0,COUNTA(A:A),1),OFFSET(A1,0,0,COUNTA(A:A),1),0),""),IF(LEN(OFFSET(A1,0,0,COUNTA(A:A),1))>0,MATCH(OFFSET(A1,0,0,COUNTA(A:A),1),OFFSET(A1,0,0,COUNTA(A:A),1),0),""))>0,1))

paste that into any cell on the same sheet as your list of names using ctrl+shift+enter

eg.
Select cell -> Select formula bar -> Paste formula -> ctrl+shift+enter

This works perfectly well. But the thing is, I'm doing this for data with more than 5000 entries, and growing. The data in column A are entered through a barcode scanner. A new entry is entered in column A every time a barcode is scanned. Say we're already in 1000th entry. Excel processes a long time (around 3-5 seconds) before you can enter/scan the 1001st barcode. Why is this so? Is there a better way?

It is getting slow since excel needs to calculate =COUNTIF(A\$1:A1,A1) over and over again.
I should copy => paste special values the values (row) 0 till 5.000.

After that those calculations are gone in the file (for row 0 till 5.000)

Thanks guys for your responses, but still it's not exactly what I'm looking for.

Will there be a difference if use an ARRAY instead of copying the formula to all the cells?

To put it simply, I just need to know "How many unique people entered the building?" Copy -> Paste Special Values is not an option since there won't be anyone to do this. (The program will be left open/operational at the entry point of the building. Reporting will be done at the end of the day.)

(In other words, reentering people should not be part of the count, but are still subjected to scan their barcodes.)

Replies
6
Views
63
Replies
1
Views
121
Replies
1
Views
101
Replies
0
Views
120
Replies
5
Views
277

1,203,454
Messages
6,055,539
Members
444,794
Latest member
HSAL

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

### Which adblocker are you using?

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

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